package com.gss.stockLedger.service;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFBorderFormatting;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.hibernate.dialect.FirebirdDialect;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.gss.stockLedger.domain.FIFOReportDTO;
import com.gss.stockLedger.domain.SummaryInv;
import com.gss.stockLedger.domain.SummaryParts;
import com.gss.stockLedger.form.ReportGenerateForm;
import com.gss.stockLedger.form.VendorWiseReportFilter;
import com.gss.stockLedger.util.ApplicationConstants;

@Service
public class ExcelCreationService {
	static Logger logger = Logger.getLogger(ExcelCreationService.class);
	
	static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd-MMM-yyyy");
	static SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("ddMMyyyyHHmmss");
	private static Font f1;
	public static Workbook createExcelFile(String sheetName, String valuationMethod, Date startDate, Date endDate, Date currentDate, String reportCategory
			, String reportType, String invType, String companyName) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();
		String strStartDate =simpleDateFormat.format(startDate);
		String strEndDate =simpleDateFormat.format(endDate);
		
		if(sheetName.equals("StockLedgerDetailed")){
			Sheet sheet = wb.createSheet(sheetName);
			
			/* added by ashish */
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 10));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 24));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 11));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 14));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 15, 24));
			sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 24));
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 2));
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 3, 24));
			sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 1));
			sheet.addMergedRegion(new CellRangeAddress(4, 4, 2, 24));
			sheet.addMergedRegion(new CellRangeAddress(5, 5, 6, 24));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 6));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 7, 10));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 11, 15));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 17, 18));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 20, 21));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 22, 24));
			/*  */
			
			CellStyle cs1 = wb.createCellStyle();
			CellStyle cs2 = wb.createCellStyle();
			CellStyle cs3 = wb.createCellStyle();
			CellStyle cs4 = wb.createCellStyle();
			CellStyle cs5 = wb.createCellStyle();
			CellStyle cs6 = wb.createCellStyle();
			CellStyle cs7 = wb.createCellStyle();

			f1 = wb.createFont();
			f1.setColor(HSSFColor.DARK_BLUE.index);
			f1.setBoldweight(Font.BOLDWEIGHT_BOLD);

			Row row0 = sheet.createRow(0); // ROW 0
			row0.createCell(0).setCellValue("Company Name:");
			Cell cell3 = row0.createCell(3);
			if(companyName.equals("RNAIPL"))
				cell3.setCellValue("RENAULT NISSAN AUTOMOTIVE INDIA PVT LTD");
			else if(companyName.equals("NMIPL"))
				cell3.setCellValue("NISSAN MOTOR INDIA PVT LTD");
			cs1.setFont(f1);
			cell3.setCellStyle(cs1);

			Row row1 = sheet.createRow(1); // ROW 1
			Cell cell112 = row1.createCell(12);
			cell112.setCellValue("STORES LEDGER - DETAILED");
			cell112.setCellStyle(cs1);

			Row row2 = sheet.createRow(2); // ROW 2 (EMPTY for border)
			cs7.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);

			Row row3 = sheet.createRow(3); // ROW 3
			Cell cell30 = row3.createCell(0);
			cell30.setCellValue("STORES LEDGER FOR:"+invType);

			Row row4 = sheet.createRow(4); // ROW 4
			Cell cell40 = row4.createCell(0);
			cell40.setCellValue("STOCK VALUATION METHOD:"+valuationMethod);

			Row row5 = sheet.createRow(5); // ROW 5
			Cell cell50 = row5.createCell(0);
			Cell cell52 = row5.createCell(2);
			
			Cell dateCell1 = row5.createCell(3);
			Cell cell54 = row5.createCell(4);
			Cell dateCell2 = row5.createCell(5);
			cell50.setCellValue("PERIOD:");
			cell52.setCellValue("FROM:");
			
			  dateCell1.setCellValue(strStartDate);
			  dateCell1.setCellType(Cell.CELL_TYPE_STRING);
			  dateCell2.setCellType(Cell.CELL_TYPE_STRING);
			  dateCell2.setCellValue(strEndDate);

			cell54.setCellValue("TO:");

			Row row6 = sheet.createRow(6); // ROW 6 (EMPTY for border)
			cs2.setBorderTop(HSSFBorderFormatting.BORDER_THICK);
			cs2.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);
			/* row6.setRowStyle(hcs2); */

			Row row7 = sheet.createRow(7); // ROW 7

			cs3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
			cs3.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs4.setFillForegroundColor(IndexedColors.PINK.getIndex());
			cs4.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs5.setFillForegroundColor(IndexedColors.AQUA.getIndex());
			cs5.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs6.setFillForegroundColor(IndexedColors.GREEN.getIndex());
			cs6.setFillPattern(CellStyle.SOLID_FOREGROUND);

			Cell cell77 = row7.createCell(7);
			Cell cell717 = row7.createCell(17);
			Cell cell720 = row7.createCell(20);
			Cell cell722 = row7.createCell(22);

			cell77.setCellValue("STORES LEDGER REFERENCE");
			cell717.setCellValue("PURCHASE/ RECEIPTS");
			cell720.setCellValue("SALE/ CONSUMPTION");
			cell722.setCellValue("CLOSING BOOK STOCK");

			cell77.setCellStyle(cs3);
			cell717.setCellStyle(cs4);
			cell720.setCellStyle(cs5);
			cell722.setCellStyle(cs6);

			row7.createCell(0).setCellStyle(cs3);
			row7.createCell(1).setCellStyle(cs3);
			row7.createCell(2).setCellStyle(cs3);
			row7.createCell(3).setCellStyle(cs3);
			row7.createCell(4).setCellStyle(cs3);
			row7.createCell(5).setCellStyle(cs3);
			row7.createCell(6).setCellStyle(cs3);
			row7.createCell(8).setCellStyle(cs3);
			row7.createCell(9).setCellStyle(cs3);
			row7.createCell(10).setCellStyle(cs3);
			row7.createCell(11).setCellStyle(cs3);
			row7.createCell(12).setCellStyle(cs3);
			row7.createCell(13).setCellStyle(cs3);
			row7.createCell(14).setCellStyle(cs3);
			row7.createCell(15).setCellStyle(cs3);
			row7.createCell(16).setCellStyle(cs4);
			row7.createCell(18).setCellStyle(cs4);
			row7.createCell(19).setCellStyle(cs5);
			row7.createCell(21).setCellStyle(cs5);
			row7.createCell(23).setCellStyle(cs6);
			row7.createCell(24).setCellStyle(cs6);
			

			Row row8 = sheet.createRow(8); // ROW 8

			Cell cell80 = row8.createCell(0);
			Cell cell81 = row8.createCell(1);
			Cell cell82 = row8.createCell(2);
			Cell cell83 = row8.createCell(3);
			Cell cell84 = row8.createCell(4);
			Cell cell85 = row8.createCell(5);
			Cell cell86 = row8.createCell(6);
			Cell cell87 = row8.createCell(7);
			Cell cell88 = row8.createCell(8);
			Cell cell89 = row8.createCell(9);
			Cell cell810 = row8.createCell(10);
			Cell cell811 = row8.createCell(11);
			Cell cell812 = row8.createCell(12);
			Cell cell813 = row8.createCell(13);
			Cell cell814 = row8.createCell(14);
			Cell cell815 = row8.createCell(15);
			Cell cell816 = row8.createCell(16);
			Cell cell817 = row8.createCell(17);
			Cell cell818 = row8.createCell(18);
			Cell cell819 = row8.createCell(19);
			Cell cell820 = row8.createCell(20);
			Cell cell821 = row8.createCell(21);
			Cell cell822 = row8.createCell(22);
			Cell cell823 = row8.createCell(23);
			Cell cell824 = row8.createCell(24);

			cell80.setCellValue("Inv Type");
			cell81.setCellValue("Material Code");
			cell82.setCellValue("Part Desc");
			cell83.setCellValue("SAP Doc No");
			cell84.setCellValue("Entry Date");
			cell85.setCellValue("Assignment No");
			cell86.setCellValue("Transaction Date");
			cell87.setCellValue("Vendor Invoice No");
			cell88.setCellValue("Invoice Date");
			cell89.setCellValue("Vendor Code");
			cell810.setCellValue("Vendor Name");
			cell811.setCellValue("Amount in FCY");
			cell812.setCellValue("FCY Unit");
			cell813.setCellValue("Purchase Type");
			cell814.setCellValue("Transaction Type");
			cell815.setCellValue("AcctType");
			cell816.setCellValue("Qty");
			cell817.setCellValue("Unit Rate");
			cell818.setCellValue(" Amount in Local Currency");
			cell819.setCellValue("Qty");
			cell820.setCellValue("Unit Rate");
			cell821.setCellValue(" Amount in Local Currency");
			cell822.setCellValue("Qty");
			cell823.setCellValue("Average Rate");
			cell824.setCellValue("Value");
			
			cell80.setCellStyle(cs3);
			cell81.setCellStyle(cs3);
			cell82.setCellStyle(cs3);
			cell83.setCellStyle(cs3);
			cell84.setCellStyle(cs3);
			cell85.setCellStyle(cs3);
			cell86.setCellStyle(cs3);
			cell87.setCellStyle(cs3);
			cell88.setCellStyle(cs3);
			cell89.setCellStyle(cs3);
			cell810.setCellStyle(cs3);
			cell811.setCellStyle(cs3);
			cell812.setCellStyle(cs3);
			cell813.setCellStyle(cs3);
			cell814.setCellStyle(cs3);
			cell815.setCellStyle(cs3);
			cell816.setCellStyle(cs4);
			cell817.setCellStyle(cs4);
			cell818.setCellStyle(cs4);
			cell819.setCellStyle(cs5);
			cell820.setCellStyle(cs5);
			cell821.setCellStyle(cs5);
			cell822.setCellStyle(cs6);
			cell823.setCellStyle(cs6);
			cell824.setCellStyle(cs6);
		}
		
		if(sheetName.equals("StockLedgerSummary-PartWise")){
			Sheet sheet = wb.createSheet(sheetName);
			
			/* added by ashish */
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 10));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 35));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 11));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 14));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 15, 35));
			sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 35));
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 3));
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 3, 35));
			sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 1));
			sheet.addMergedRegion(new CellRangeAddress(4, 4, 6, 35));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 0, 2));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 3, 4));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 5, 10));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 11, 13));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 14, 18));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 19, 25));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 26, 28));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 29, 32));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 33, 35));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 2));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 3, 4));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 5, 6));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 7, 8));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 9, 10));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 11, 12));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 13, 14));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 15, 16));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 17, 18));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 19, 20));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 21, 22));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 23, 24));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 25, 26));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 27, 28));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 29, 30));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 31, 32));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 33, 35));
			/*  */ 
			
			CellStyle cs1 = wb.createCellStyle();
			CellStyle cs2 = wb.createCellStyle();
			CellStyle cs3 = wb.createCellStyle();
			CellStyle cs4 = wb.createCellStyle();
			CellStyle cs5 = wb.createCellStyle();
			CellStyle cs6 = wb.createCellStyle();
			CellStyle cs7 = wb.createCellStyle();
			CellStyle cs8 = wb.createCellStyle();
			CellStyle cs9 = wb.createCellStyle();

			f1 = wb.createFont();
			f1.setColor(HSSFColor.DARK_BLUE.index);
			f1.setBoldweight(Font.BOLDWEIGHT_BOLD);

			Row row0 = sheet.createRow(0); // ROW 0
			row0.createCell(0).setCellValue("Company Name:");
			Cell cell3 = row0.createCell(3);
			if(companyName.equals("RNAIPL"))
				cell3.setCellValue("RENAULT NISSAN AUTOMOTIVE INDIA PVT LTD");
			else if(companyName.equals("NMIPL"))
				cell3.setCellValue("NISSAN MOTOR INDIA PVT LTD");
			cs1.setFont(f1);
			cell3.setCellStyle(cs1);

			Row row1 = sheet.createRow(1); // ROW 1
			Cell cell112 = row1.createCell(12);
			cell112.setCellValue("STORES LEDGER - SUMMARY - PART WISE");
			cell112.setCellStyle(cs1);

			Row row2 = sheet.createRow(2); // ROW 2 (EMPTY for border)
			cs7.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);


			/*Row row3 = sheet.createRow(3); // ROW 3
			Cell cell30 = row3.createCell(0);
			cell30.setCellValue("STORES LEDGER FOR:");*/

			Row row3 = sheet.createRow(3); // ROW 3
			Cell cell30 = row3.createCell(0);
			cell30.setCellValue("STOCK VALUATION METHOD:"+valuationMethod);

			Row row4 = sheet.createRow(4); // ROW 4
			Cell cell40 = row4.createCell(0);
			Cell cell42 = row4.createCell(2);
			Cell dateCell1 = row4.createCell(3);
			Cell cell44 = row4.createCell(4);
			Cell dateCell2 = row4.createCell(5);
			cell40.setCellValue("PERIOD:");
			cell42.setCellValue("FROM:");
			
			 dateCell1.setCellValue(strStartDate);
			  dateCell1.setCellType(Cell.CELL_TYPE_STRING);
			  dateCell2.setCellType(Cell.CELL_TYPE_STRING);
			  dateCell2.setCellValue(strEndDate);

			cell44.setCellValue("TO:");

			Row row5 = sheet.createRow(5); // ROW 5 (EMPTY for border)
			cs2.setBorderTop(HSSFBorderFormatting.BORDER_THICK);
			cs2.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);
			/* row5.setRowStyle(hcs2); */

			Row row6 = sheet.createRow(6); // ROW 6

			cs3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
			cs3.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs4.setFillForegroundColor(IndexedColors.PINK.getIndex());
			cs4.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs5.setFillForegroundColor(IndexedColors.AQUA.getIndex());
			cs5.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs6.setFillForegroundColor(IndexedColors.GREEN.getIndex());
			cs6.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs8.setFillForegroundColor(IndexedColors.RED.getIndex());
			cs8.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs9.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
			cs9.setFillPattern(CellStyle.SOLID_FOREGROUND);

			Cell cell60 = row6.createCell(0);
			Cell cell63 = row6.createCell(3);
			Cell cell611 = row6.createCell(11);
			Cell cell626 = row6.createCell(26);
			Cell cell633 = row6.createCell(33);

			cell60.setCellValue("STORES LEDGER REFERENCE");
			cell63.setCellValue("STOCK BALANCE");
			cell611.setCellValue("PURCHASE/ RECEIPTS");
			cell626.setCellValue("SALE/ CONSUMPTION");
			cell633.setCellValue("STOCK BALANCE");

			cell60.setCellStyle(cs3);
			cell63.setCellStyle(cs4);
			cell611.setCellStyle(cs5);
			cell626.setCellStyle(cs8);
			cell633.setCellStyle(cs6);
			
			row6.createCell(5).setCellStyle(cs5);
			row6.createCell(6).setCellStyle(cs5);
			row6.createCell(7).setCellStyle(cs5);
			row6.createCell(8).setCellStyle(cs5);
			row6.createCell(9).setCellStyle(cs5);
			row6.createCell(10).setCellStyle(cs5);
			row6.createCell(14).setCellStyle(cs5);
			row6.createCell(15).setCellStyle(cs5);
			row6.createCell(16).setCellStyle(cs5);
			row6.createCell(17).setCellStyle(cs5);
			row6.createCell(18).setCellStyle(cs5);
			row6.createCell(19).setCellStyle(cs8);
			row6.createCell(20).setCellStyle(cs8);
			row6.createCell(21).setCellStyle(cs8);
			row6.createCell(22).setCellStyle(cs8);
			row6.createCell(23).setCellStyle(cs8);
			row6.createCell(24).setCellStyle(cs8);
			row6.createCell(25).setCellStyle(cs8);
			row6.createCell(29).setCellStyle(cs8);
			row6.createCell(30).setCellStyle(cs8);
			row6.createCell(31).setCellStyle(cs8);
			row6.createCell(32).setCellStyle(cs8);
			
			
			Row row7 = sheet.createRow(7); // ROW 7
			
			row7.createCell(0).setCellStyle(cs3);
			row7.createCell(1).setCellStyle(cs3);
			row7.createCell(2).setCellStyle(cs3);
			
			Cell cell73 = row7.createCell(3);
			row7.createCell(4);
			Cell cell75 = row7.createCell(5);
			row7.createCell(6);
			Cell cell77 = row7.createCell(7);
			row7.createCell(8);
			Cell cell79 = row7.createCell(9);
			row7.createCell(10);
			Cell cell711 = row7.createCell(11);
			row7.createCell(12);
			Cell cell713 = row7.createCell(13);
			row7.createCell(14);
			Cell cell715 = row7.createCell(15);
			row7.createCell(16);
			Cell cell717 = row7.createCell(17);
			row7.createCell(18);
			Cell cell719 = row7.createCell(19);
			row7.createCell(20);
			Cell cell721 = row7.createCell(21);
			row7.createCell(22);
			Cell cell723 = row7.createCell(23);
			row7.createCell(24);
			Cell cell725 = row7.createCell(25);
			row7.createCell(26);
			Cell cell727 = row7.createCell(27);
			row7.createCell(28);
			Cell cell729 = row7.createCell(29);
			row7.createCell(30);
			Cell cell731 = row7.createCell(31);
			row7.createCell(32);
			Cell cell733 = row7.createCell(33);
			row7.createCell(34);
			row7.createCell(35);
			
			
			cell73.setCellValue("OPENING BOOK STOCK");
			cell75.setCellValue("ZGRIR");
			cell77.setCellValue("CUSTOM");
			cell79.setCellValue("COSTPRICE");
			cell711.setCellValue("INVADJ");
			cell713.setCellValue("FOREX");
			cell715.setCellValue("FI ADJ");
			cell717.setCellValue("RETRO");
			cell719.setCellValue("SALES");
			cell721.setCellValue("CONS");
			cell723.setCellValue("FOC");
			cell725.setCellValue("RTV");
			cell727.setCellValue("SCRAP");
			cell729.setCellValue("CAPTIVE");
			cell731.setCellValue("OTHER CONS");
			cell733.setCellValue("CLOSING BOOK STOCK");
			
			cell73.setCellStyle(cs4);
			cell75.setCellStyle(cs3);
			cell77.setCellStyle(cs3);
			cell79.setCellStyle(cs3);
			cell711.setCellStyle(cs3);
			cell713.setCellStyle(cs3);
			cell715.setCellStyle(cs3);
			cell717.setCellStyle(cs3);
			cell719.setCellStyle(cs9);
			cell721.setCellStyle(cs9);
			cell723.setCellStyle(cs9);
			cell723.setCellStyle(cs9);
			cell725.setCellStyle(cs9);
			cell727.setCellStyle(cs9);
			cell729.setCellStyle(cs9);
			cell731.setCellStyle(cs9);
			cell733.setCellStyle(cs6);
			
			
			Row row8 = sheet.createRow(8); // ROW 8

			Cell cell80 = row8.createCell(0);
			Cell cell81 = row8.createCell(1);
			Cell cell82 = row8.createCell(2);
			Cell cell83 = row8.createCell(3);
			Cell cell84 = row8.createCell(4);
			Cell cell85 = row8.createCell(5);
			Cell cell86 = row8.createCell(6);
			Cell cell87 = row8.createCell(7);
			Cell cell88 = row8.createCell(8);
			Cell cell89 = row8.createCell(9);
			Cell cell810 = row8.createCell(10);
			Cell cell811 = row8.createCell(11);
			Cell cell812 = row8.createCell(12);
			Cell cell813 = row8.createCell(13);
			Cell cell814 = row8.createCell(14);
			Cell cell815 = row8.createCell(15);
			Cell cell816 = row8.createCell(16);
			Cell cell817 = row8.createCell(17);
			Cell cell818 = row8.createCell(18);
			Cell cell819 = row8.createCell(19);
			Cell cell820 = row8.createCell(20);
			Cell cell821 = row8.createCell(21);
			Cell cell822 = row8.createCell(22);
			Cell cell823 = row8.createCell(23);
			Cell cell824 = row8.createCell(24);
			Cell cell825 = row8.createCell(25);
			Cell cell826 = row8.createCell(26);
			Cell cell827 = row8.createCell(27);
			Cell cell828 = row8.createCell(28);
			Cell cell829 = row8.createCell(29);
			Cell cell830 = row8.createCell(30);
			Cell cell831 = row8.createCell(31);
			Cell cell832 = row8.createCell(32);
			Cell cell833 = row8.createCell(33);
			Cell cell834 = row8.createCell(34);
			Cell cell835 = row8.createCell(35);

			cell80.setCellValue("Inv Type");
			cell81.setCellValue("Material Code");
			cell82.setCellValue("Part Desc");
			cell83.setCellValue("Qty");
			cell84.setCellValue("Amount");
			cell85.setCellValue("Qty");
			cell86.setCellValue("Amount");
			cell87.setCellValue("Qty");
			cell88.setCellValue("Amount");
			cell89.setCellValue("Qty");
			cell810.setCellValue("Amount");
			cell811.setCellValue("Qty");
			cell812.setCellValue("Amount");
			cell813.setCellValue("Qty");
			cell814.setCellValue("Amount");
			cell815.setCellValue("Qty");
			cell816.setCellValue("Amount");
			cell817.setCellValue("Qty");
			cell818.setCellValue("Amount");
			cell819.setCellValue("Qty");
			cell820.setCellValue("Amount");
			cell821.setCellValue("Qty");
			cell822.setCellValue("Amount");
			cell823.setCellValue("Qty");
			cell824.setCellValue("Amount");
			cell825.setCellValue("Qty");
			cell826.setCellValue("Amount");
			cell827.setCellValue("Qty");
			cell828.setCellValue("Amount");
			cell829.setCellValue("Qty");
			cell830.setCellValue("Amount");
			cell831.setCellValue("Qty");
			cell832.setCellValue("Amount");
			cell833.setCellValue("Qty");
			cell834.setCellValue("Average Rate"); 
			cell835.setCellValue("Value");
			
			cell80.setCellStyle(cs3);
			cell81.setCellStyle(cs3);
			cell82.setCellStyle(cs3);
			cell83.setCellStyle(cs4);
			cell84.setCellStyle(cs4);
			cell85.setCellStyle(cs3);
			cell86.setCellStyle(cs3);
			cell87.setCellStyle(cs3);
			cell88.setCellStyle(cs3);
			cell89.setCellStyle(cs3);
			cell810.setCellStyle(cs3);
			cell811.setCellStyle(cs3);
			cell812.setCellStyle(cs3);
			cell813.setCellStyle(cs3);
			cell814.setCellStyle(cs3);
			cell815.setCellStyle(cs3);
			cell816.setCellStyle(cs3);
			cell817.setCellStyle(cs3);
			cell818.setCellStyle(cs3);
			cell819.setCellStyle(cs3);
			cell820.setCellStyle(cs3);
			cell821.setCellStyle(cs3);
			cell822.setCellStyle(cs3);
			cell823.setCellStyle(cs3);
			cell824.setCellStyle(cs3);
			cell825.setCellStyle(cs3);
			cell826.setCellStyle(cs3);
			cell827.setCellStyle(cs3);
			cell828.setCellStyle(cs3);
			cell829.setCellStyle(cs3);
			cell830.setCellStyle(cs3);
			cell831.setCellStyle(cs3);
			cell832.setCellStyle(cs3);
			cell833.setCellStyle(cs9);
			cell834.setCellStyle(cs9);
			cell835.setCellStyle(cs9);
		}
		else if(sheetName.equals("StockLedgerSummary-InventoryTypeWise"))
		{
			Sheet sheet = wb.createSheet(sheetName);
			
			/* added by ashish */
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 10));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 35));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 11));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 14));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 15, 35));
			sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 35));
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 3));
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 3, 35));
			sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 1));
			sheet.addMergedRegion(new CellRangeAddress(4, 4, 6, 35));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 0, 2));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 3, 4));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 5, 10));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 11, 13));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 14, 18));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 19, 23));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 24, 26));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 27, 32));
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 33, 35));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 2));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 3, 4));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 5, 6));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 7, 8));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 9, 10));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 11, 12));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 13, 14));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 15, 16));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 17, 18));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 19, 20));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 21, 22));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 23, 24));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 25, 26));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 27, 28));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 29, 30));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 31, 32));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 33, 35));
			sheet.addMergedRegion(new CellRangeAddress(8, 8, 0, 2));
			/*  */ 
			
			CellStyle cs1 = wb.createCellStyle();
			CellStyle cs2 = wb.createCellStyle();
			CellStyle cs3 = wb.createCellStyle();
			CellStyle cs4 = wb.createCellStyle();
			CellStyle cs5 = wb.createCellStyle();
			CellStyle cs6 = wb.createCellStyle();
			CellStyle cs7 = wb.createCellStyle();
			CellStyle cs8 = wb.createCellStyle();
			CellStyle cs9 = wb.createCellStyle();

			f1 = wb.createFont();
			f1.setColor(HSSFColor.DARK_BLUE.index);
			f1.setBoldweight(Font.BOLDWEIGHT_BOLD);

			Row row0 = sheet.createRow(0); // ROW 0
			row0.createCell(0).setCellValue("Company Name:");
			Cell cell3 = row0.createCell(3);
			if(companyName.equals("RNAIPL"))
				cell3.setCellValue("RENAULT NISSAN AUTOMOTIVE INDIA PVT LTD");
			else if(companyName.equals("NMIPL"))
				cell3.setCellValue("NISSAN MOTOR INDIA PVT LTD");
			cs1.setFont(f1);
			cell3.setCellStyle(cs1);

			Row row1 = sheet.createRow(1); // ROW 1
			Cell cell112 = row1.createCell(12);
			cell112.setCellValue("STORES LEDGER - SUMMARY - PART WISE");
			cell112.setCellStyle(cs1);

			Row row2 = sheet.createRow(2); // ROW 2 (EMPTY for border)
			cs7.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);


			/*Row row3 = sheet.createRow(3); // ROW 3
			Cell cell30 = row3.createCell(0);
			cell30.setCellValue("STORES LEDGER FOR:");*/

			Row row3 = sheet.createRow(3); // ROW 3
			Cell cell30 = row3.createCell(0);
			cell30.setCellValue("STOCK VALUATION METHOD:"+valuationMethod);

			Row row4 = sheet.createRow(4); // ROW 4
			Cell cell40 = row4.createCell(0);
			Cell cell42 = row4.createCell(2);
			Cell dateCell1 = row4.createCell(3);
			Cell cell44 = row4.createCell(4);
			Cell dateCell2 = row4.createCell(5);
			cell40.setCellValue("PERIOD:");
			cell42.setCellValue("FROM:");
			
			 dateCell1.setCellValue(strStartDate);
			  dateCell1.setCellType(Cell.CELL_TYPE_STRING);
			  dateCell2.setCellType(Cell.CELL_TYPE_STRING);
			  dateCell2.setCellValue(strEndDate);
			
			cell44.setCellValue("TO:");

			Row row5 = sheet.createRow(5); // ROW 5 (EMPTY for border)
			cs2.setBorderTop(HSSFBorderFormatting.BORDER_THICK);
			cs2.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);
			/* row5.setRowStyle(hcs2); */

			Row row6 = sheet.createRow(6); // ROW 6

			cs3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
			cs3.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs4.setFillForegroundColor(IndexedColors.PINK.getIndex());
			cs4.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs5.setFillForegroundColor(IndexedColors.AQUA.getIndex());
			cs5.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs6.setFillForegroundColor(IndexedColors.GREEN.getIndex());
			cs6.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs8.setFillForegroundColor(IndexedColors.RED.getIndex());
			cs8.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs9.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
			cs9.setFillPattern(CellStyle.SOLID_FOREGROUND);

			Cell cell60 = row6.createCell(0);
			Cell cell63 = row6.createCell(3);
			Cell cell611 = row6.createCell(11);
			Cell cell624 = row6.createCell(24);
			Cell cell633 = row6.createCell(33);

			cell60.setCellValue("STORES LEDGER REFERENCE");
			cell63.setCellValue("STOCK BALANCE");
			cell611.setCellValue("PURCHASE/ RECEIPTS");
			cell624.setCellValue("SALE/ CONSUMPTION");
			cell633.setCellValue("STOCK BALANCE");

			cell60.setCellStyle(cs3);
			cell63.setCellStyle(cs4);
			cell611.setCellStyle(cs5);
			cell624.setCellStyle(cs8);
			cell633.setCellStyle(cs6);
			
			row6.createCell(5).setCellStyle(cs5);
			row6.createCell(6).setCellStyle(cs5);
			row6.createCell(7).setCellStyle(cs5);
			row6.createCell(8).setCellStyle(cs5);
			row6.createCell(9).setCellStyle(cs5);
			row6.createCell(10).setCellStyle(cs5);
			row6.createCell(14).setCellStyle(cs5);
			row6.createCell(15).setCellStyle(cs5);
			row6.createCell(16).setCellStyle(cs5);
			row6.createCell(17).setCellStyle(cs5);
			row6.createCell(18).setCellStyle(cs5);
			row6.createCell(10).setCellStyle(cs8);
			row6.createCell(19).setCellStyle(cs8);
			row6.createCell(20).setCellStyle(cs8);
			row6.createCell(21).setCellStyle(cs8);
			row6.createCell(22).setCellStyle(cs8);
			row6.createCell(23).setCellStyle(cs8);
			row6.createCell(27).setCellStyle(cs8);
			row6.createCell(28).setCellStyle(cs8);
			row6.createCell(29).setCellStyle(cs8);
			row6.createCell(30).setCellStyle(cs8);
			
			
			Row row7 = sheet.createRow(7); // ROW 7
			
			row7.createCell(0).setCellStyle(cs3);
			row7.createCell(1).setCellStyle(cs3);
			row7.createCell(2).setCellStyle(cs3);
			
			Cell cell73 = row7.createCell(3);
			row7.createCell(4);
			Cell cell75 = row7.createCell(5);
			row7.createCell(6);
			Cell cell77 = row7.createCell(7);
			row7.createCell(8);
			Cell cell79 = row7.createCell(9);
			row7.createCell(10);
			Cell cell711 = row7.createCell(11);
			row7.createCell(12);
			Cell cell713 = row7.createCell(13);
			row7.createCell(14);
			Cell cell715 = row7.createCell(15);
			row7.createCell(16);
			Cell cell717 = row7.createCell(17);
			row7.createCell(18);
			Cell cell719 = row7.createCell(19);
			row7.createCell(20);
			Cell cell721 = row7.createCell(21);
			row7.createCell(22);
			Cell cell723 = row7.createCell(23);
			row7.createCell(24);
			Cell cell725 = row7.createCell(25);
			row7.createCell(26);
			Cell cell727 = row7.createCell(27);
			row7.createCell(28);
			Cell cell729 = row7.createCell(29);
			row7.createCell(30);
			Cell cell731 = row7.createCell(31);
			row7.createCell(32);
			Cell cell733 = row7.createCell(33);
			row7.createCell(34);
			row7.createCell(35);
			
			
			cell73.setCellValue("OPENING BOOK STOCK");
			cell75.setCellValue("ZGRIR");
			cell77.setCellValue("CUSTOM");
			cell79.setCellValue("COSTPRICE");
			cell711.setCellValue("INVADJ");
			cell713.setCellValue("FOREX");
			cell715.setCellValue("FI ADJ");
			cell717.setCellValue("RETRO");
			
			cell719.setCellValue("SALES");
			
			cell721.setCellValue("CONS");
			cell723.setCellValue("FOC");
			cell725.setCellValue("RTV");
			cell727.setCellValue("SCRAP");
			cell729.setCellValue("CAPTIVE");
			cell731.setCellValue("OTHER CONS");
			cell733.setCellValue("CLOSING BOOK STOCK");
			
			cell73.setCellStyle(cs4);
			cell75.setCellStyle(cs3);
			cell77.setCellStyle(cs3);
			cell79.setCellStyle(cs3);
			cell711.setCellStyle(cs3);
			cell713.setCellStyle(cs3);
			cell715.setCellStyle(cs3);
			cell717.setCellStyle(cs3);
			cell719.setCellStyle(cs9);
			cell721.setCellStyle(cs9);
			cell723.setCellStyle(cs9);
			cell723.setCellStyle(cs9);
			cell725.setCellStyle(cs9);
			cell727.setCellStyle(cs9);
			cell729.setCellStyle(cs9);
			cell731.setCellStyle(cs9);
			cell733.setCellStyle(cs6);
			
			
			Row row8 = sheet.createRow(8); // ROW 8

			Cell cell80 = row8.createCell(0);
			Cell cell81 = row8.createCell(1);
			Cell cell82 = row8.createCell(2);
			Cell cell83 = row8.createCell(3);
			Cell cell84 = row8.createCell(4);
			Cell cell85 = row8.createCell(5);
			Cell cell86 = row8.createCell(6);
			Cell cell87 = row8.createCell(7);
			Cell cell88 = row8.createCell(8);
			Cell cell89 = row8.createCell(9);
			Cell cell810 = row8.createCell(10);
			Cell cell811 = row8.createCell(11);
			Cell cell812 = row8.createCell(12);
			Cell cell813 = row8.createCell(13);
			Cell cell814 = row8.createCell(14);
			Cell cell815 = row8.createCell(15);
			Cell cell816 = row8.createCell(16);
			Cell cell817 = row8.createCell(17);
			Cell cell818 = row8.createCell(18);
			Cell cell819 = row8.createCell(19);
			Cell cell820 = row8.createCell(20);
			Cell cell821 = row8.createCell(21);
			Cell cell822 = row8.createCell(22);
			Cell cell823 = row8.createCell(23);
			Cell cell824 = row8.createCell(24);
			Cell cell825 = row8.createCell(25);
			Cell cell826 = row8.createCell(26);
			Cell cell827 = row8.createCell(27);
			Cell cell828 = row8.createCell(28);
			Cell cell829 = row8.createCell(29);
			Cell cell830 = row8.createCell(30);
			Cell cell831 = row8.createCell(31);
			Cell cell832 = row8.createCell(32);
			Cell cell833 = row8.createCell(33);
			Cell cell834 = row8.createCell(34);
			Cell cell835 = row8.createCell(35);

			cell80.setCellValue("Inv Type");
			//cell81.setCellValue("Material Code");
			//cell82.setCellValue("Part Desc");
			cell83.setCellValue("Qty");
			cell84.setCellValue("Amount");
			cell85.setCellValue("Qty");
			cell86.setCellValue("Amount");
			cell87.setCellValue("Qty");
			cell88.setCellValue("Amount");
			cell89.setCellValue("Qty");
			cell810.setCellValue("Amount");
			cell811.setCellValue("Qty");
			cell812.setCellValue("Amount");
			cell813.setCellValue("Qty");
			cell814.setCellValue("Amount");
			cell815.setCellValue("Qty");
			cell816.setCellValue("Amount");
			cell817.setCellValue("Qty");
			cell818.setCellValue("Amount");
			cell819.setCellValue("Qty");
			cell820.setCellValue("Amount");
			cell821.setCellValue("Qty");
			cell822.setCellValue("Amount");
			cell823.setCellValue("Qty");
			cell824.setCellValue("Amount");
			cell825.setCellValue("Qty");
			cell826.setCellValue("Amount");
			cell827.setCellValue("Qty");
			cell828.setCellValue("Amount");
			cell829.setCellValue("Qty");
			cell830.setCellValue("Amount");
			cell831.setCellValue("Qty");
			cell832.setCellValue("Amount");
			cell833.setCellValue("Qty");
			cell834.setCellValue("Average Rate"); 
			cell835.setCellValue("Value");
			
			cell80.setCellStyle(cs3);
			//cell81.setCellStyle(cs3);
			//cell82.setCellStyle(cs3);
			cell83.setCellStyle(cs4);
			cell84.setCellStyle(cs4);
			cell85.setCellStyle(cs3);
			cell86.setCellStyle(cs3);
			cell87.setCellStyle(cs3);
			cell88.setCellStyle(cs3);
			cell89.setCellStyle(cs3);
			cell810.setCellStyle(cs3);
			cell811.setCellStyle(cs3);
			cell812.setCellStyle(cs3);
			cell813.setCellStyle(cs3);
			cell814.setCellStyle(cs3);
			cell815.setCellStyle(cs3);
			cell816.setCellStyle(cs3);
			cell817.setCellStyle(cs3);
			cell818.setCellStyle(cs3);
			cell819.setCellStyle(cs3);
			cell820.setCellStyle(cs3);
			cell821.setCellStyle(cs3);
			cell822.setCellStyle(cs3);
			cell823.setCellStyle(cs3);
			cell824.setCellStyle(cs3);
			cell825.setCellStyle(cs3);
			cell826.setCellStyle(cs3);
			cell827.setCellStyle(cs3);
			cell828.setCellStyle(cs3);
			cell829.setCellStyle(cs3);
			cell830.setCellStyle(cs3);
			cell831.setCellStyle(cs3);
			cell832.setCellStyle(cs3);
			cell833.setCellStyle(cs9);
			cell834.setCellStyle(cs9);
			cell835.setCellStyle(cs9);
		}
		else if (sheetName.equals(ApplicationConstants.vendorWiseSheetName)){
			
				Sheet sheet = wb.createSheet(sheetName);
				
				/* added by ashish */
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 10));
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 16));
				sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 11));
				sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 14));
				sheet.addMergedRegion(new CellRangeAddress(1, 1, 15, 16));
				sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 24));
				sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 1));
				sheet.addMergedRegion(new CellRangeAddress(3, 3, 6, 16));
				sheet.addMergedRegion(new CellRangeAddress(5, 5, 6, 16));
				sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 4));
				sheet.addMergedRegion(new CellRangeAddress(7, 7, 5, 8));
				sheet.addMergedRegion(new CellRangeAddress(7, 7, 9, 13));
				sheet.addMergedRegion(new CellRangeAddress(7, 7, 14, 16));
				/*  */
				
				CellStyle cs1 = wb.createCellStyle();
				CellStyle cs2 = wb.createCellStyle();
				CellStyle cs3 = wb.createCellStyle();
				CellStyle cs4 = wb.createCellStyle();
				CellStyle cs5 = wb.createCellStyle();
				CellStyle cs6 = wb.createCellStyle();
				CellStyle cs7 = wb.createCellStyle();

				f1 = wb.createFont();
				f1.setColor(HSSFColor.DARK_BLUE.index);
				f1.setBoldweight(Font.BOLDWEIGHT_BOLD);

				Row row0 = sheet.createRow(0); // ROW 0
				row0.createCell(0).setCellValue("Company Name:");
				Cell cell3 = row0.createCell(3);
				if(companyName.equals("RNAIPL"))
					cell3.setCellValue("RENAULT NISSAN AUTOMOTIVE INDIA PVT LTD");
				else if(companyName.equals("NMIPL"))
					cell3.setCellValue("NISSAN MOTOR INDIA PVT LTD");
				cs1.setFont(f1);
				cell3.setCellStyle(cs1);

				Row row1 = sheet.createRow(1); // ROW 1
				Cell cell112 = row1.createCell(12);
				cell112.setCellValue("VENDOR WISE PURCHASE REPORT");
				cell112.setCellStyle(cs1);

				Row row2 = sheet.createRow(2); // ROW 2 (EMPTY for border)
				cs7.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);

				Row row3 = sheet.createRow(3); // ROW 3
				Cell cell30 = row3.createCell(0);
				Cell cell32 = row3.createCell(2);
				
				Cell dateCell1 = row3.createCell(3);
				Cell cell34 = row3.createCell(4);
				Cell dateCell2 = row3.createCell(5);
				cell30.setCellValue("PERIOD:");
				cell32.setCellValue("FROM:");
				
			  dateCell1.setCellValue(strStartDate);
				  dateCell1.setCellType(Cell.CELL_TYPE_STRING);
				  
				  cell34.setCellValue("TO:");
				  
				  dateCell2.setCellValue(strEndDate);
				  dateCell2.setCellType(Cell.CELL_TYPE_STRING);
				  
				

				Row row5 = sheet.createRow(5); // ROW 5 (EMPTY for border)
				cs2.setBorderTop(HSSFBorderFormatting.BORDER_THICK);
				cs2.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);
				/* row5.setRowStyle(hcs2); */

				Row row7 = sheet.createRow(7); // ROW 7

				cs3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
				cs3.setFillPattern(CellStyle.SOLID_FOREGROUND);
				cs4.setFillForegroundColor(IndexedColors.PINK.getIndex());
				cs4.setFillPattern(CellStyle.SOLID_FOREGROUND);
				cs5.setFillForegroundColor(IndexedColors.AQUA.getIndex());
				cs5.setFillPattern(CellStyle.SOLID_FOREGROUND);
				cs6.setFillForegroundColor(IndexedColors.GREEN.getIndex());
				cs6.setFillPattern(CellStyle.SOLID_FOREGROUND);

				Cell cell75 = row7.createCell(5);
				Cell cell714 = row7.createCell(14);

				cell75.setCellValue("STORES LEDGER REFERENCE");
				cell714.setCellValue("PURCHASE/ RECEIPTS");

				cell75.setCellStyle(cs3);
				cell714.setCellStyle(cs4);

				row7.createCell(0).setCellStyle(cs3);
				row7.createCell(1).setCellStyle(cs3);
				row7.createCell(2).setCellStyle(cs3);
				row7.createCell(3).setCellStyle(cs3);
				row7.createCell(4).setCellStyle(cs3);
				//row7.createCell(5).setCellStyle(cs3);
				row7.createCell(6).setCellStyle(cs3);
				row7.createCell(7).setCellStyle(cs3);
				row7.createCell(8).setCellStyle(cs3);
				row7.createCell(9).setCellStyle(cs3);
				row7.createCell(10).setCellStyle(cs3);
				row7.createCell(11).setCellStyle(cs3);
				row7.createCell(12).setCellStyle(cs3);
				row7.createCell(13).setCellStyle(cs3);
				row7.createCell(15).setCellStyle(cs4);
				row7.createCell(16).setCellStyle(cs4);
				

				Row row8 = sheet.createRow(8); // ROW 8

				Cell cell80 = row8.createCell(0);
				Cell cell81 = row8.createCell(1);
				Cell cell82 = row8.createCell(2);
				Cell cell83 = row8.createCell(3);
				Cell cell84 = row8.createCell(4);
				Cell cell85 = row8.createCell(5);
				Cell cell86 = row8.createCell(6);
				Cell cell87 = row8.createCell(7);
				Cell cell88 = row8.createCell(8);
				Cell cell89 = row8.createCell(9);
				Cell cell810 = row8.createCell(10);
				Cell cell811 = row8.createCell(11);
				Cell cell812 = row8.createCell(12);
				Cell cell813 = row8.createCell(13);
				Cell cell814 = row8.createCell(14);
				Cell cell815 = row8.createCell(15);
				Cell cell816 = row8.createCell(16);

				cell80.setCellValue("Inv Type");
				cell81.setCellValue("Material Code");
				cell82.setCellValue("Part Desc");
				cell83.setCellValue("SAP Doc No");
				cell84.setCellValue("Entry Date");
				cell85.setCellValue("Assignment No");
				cell86.setCellValue("Transaction Date");
				cell87.setCellValue("Vendor Invoice No");
				cell88.setCellValue("Invoice Date");
				cell89.setCellValue("Amount in FCY");
				cell810.setCellValue("FCY Unit");
				cell811.setCellValue("Purchase Type");
				cell812.setCellValue("Transaction Type");
				cell813.setCellValue("AcctType");
				cell814.setCellValue("Qty");
				cell815.setCellValue("Unit Rate");
				cell816.setCellValue(" Amount in Local Currency");
						
				cell80.setCellStyle(cs3);
				cell81.setCellStyle(cs3);
				cell82.setCellStyle(cs3);
				cell83.setCellStyle(cs3);
				cell84.setCellStyle(cs3);
				cell85.setCellStyle(cs3);
				cell86.setCellStyle(cs3);
				cell87.setCellStyle(cs3);
				cell88.setCellStyle(cs3);
				cell89.setCellStyle(cs3);
				cell810.setCellStyle(cs3);
				cell811.setCellStyle(cs3);
				cell812.setCellStyle(cs3);
				cell813.setCellStyle(cs3);
				cell814.setCellStyle(cs4);
				cell815.setCellStyle(cs4);
				cell816.setCellStyle(cs4);
				
			
		}
	
		String pathName = null;
		
		if(reportCategory.equals("StockLedger")){
			pathName = ApplicationConstants.pathToExcel+ System.getProperty("file.separator") + valuationMethod + System.getProperty("file.separator") 
						+  reportCategory +  System.getProperty("file.separator") + reportType + System.getProperty("file.separator") 
						+ simpleDateFormat.format(currentDate) + System.getProperty("file.separator");
		}
		else if(reportCategory.equals("") && sheetName.equals(ApplicationConstants.vendorWiseSheetName)){
			pathName = ApplicationConstants.pathToExcel+ System.getProperty("file.separator") + ApplicationConstants.vendorWiseSheetName
					+ System.getProperty("file.separator") + simpleDateFormat.format(currentDate) + System.getProperty("file.separator");
		}
		else{
			pathName = ApplicationConstants.pathToExcel+ System.getProperty("file.separator") 
					+ simpleDateFormat.format(currentDate) + System.getProperty("file.separator");
		}
		String fileName = valuationMethod + simpleDateFormat.format(startDate) + simpleDateFormat.format(endDate)
				+ simpleDateFormat2.format(currentDate) + ".xls";
		
		FileOutputStream fileOut;
		File file = new File(pathName);
		
		if(!file.exists()){
			file.mkdirs();
			fileOut = new FileOutputStream(file +"/"+ fileName);
		}
		else{
			fileOut = new FileOutputStream(file +"/"+ fileName);
		}
		
		
		wb.write(fileOut);
		fileOut.close();
		
		return wb;
	}

	public static Integer appendVendorWiseSheet(Integer excelRowCounter, Workbook workbook, List<FIFOReportDTO> finalReportList, 
			VendorWiseReportFilter vendorWiseReportFilter, Date date2) throws IOException{
		
		String sheetName = null;
		
		if(workbook.getNumberOfSheets() > 1){
			sheetName = ApplicationConstants.vendorWiseSheetName + "_" + (workbook.getNumberOfSheets()-1);
		}else{
			sheetName = ApplicationConstants.vendorWiseSheetName; 
		}
		
		Sheet sheet = workbook.getSheet(sheetName);
		
		for(FIFOReportDTO fifoReportDTO : finalReportList){
			
			if(excelRowCounter > 50000){ 
				sheetName = ApplicationConstants.vendorWiseSheetName + "_" + workbook.getNumberOfSheets();
				workbook.createSheet(sheetName);
				createVendorWiseSheetHeaders(workbook, sheetName, vendorWiseReportFilter.getCompanyName(), vendorWiseReportFilter.getStartDate(), vendorWiseReportFilter.getEndDate());
				excelRowCounter = 10;
				sheet = workbook.getSheet(sheetName);
			}
			
			Row row = sheet.createRow(excelRowCounter);
			
			Cell cell0 = row.createCell(0);
			Cell cell1 = row.createCell(1);
			Cell cell2 = row.createCell(2);
			Cell cell3 = row.createCell(3);
			Cell cell4 = row.createCell(4);
			Cell cell5 = row.createCell(5);
			Cell cell6 = row.createCell(6);
			Cell cell7 = row.createCell(7);
			Cell cell8 = row.createCell(8);
			Cell cell9 = row.createCell(9);
			Cell cell10 = row.createCell(10);
			Cell cell11 = row.createCell(11);
			Cell cell12 = row.createCell(12);
			Cell cell13 = row.createCell(13);
			Cell cell14 = row.createCell(14);
			Cell cell15 = row.createCell(15);
			Cell cell16 = row.createCell(16);
			
			
			if(null != fifoReportDTO.getInventoryType())
				cell0.setCellValue(fifoReportDTO.getInventoryType());
			if(null != fifoReportDTO.getPartId())
				cell1.setCellValue(fifoReportDTO.getPartId());
			if(null != fifoReportDTO.getPartDesc())
				cell2.setCellValue(fifoReportDTO.getPartDesc());
			if(null != fifoReportDTO.getSapDocNo())
				cell3.setCellValue(fifoReportDTO.getSapDocNo());
			if(null != fifoReportDTO.getSapEntryDate())
				cell4.setCellValue(fifoReportDTO.getSapEntryDate());
			if(null != fifoReportDTO.getAssignmentId())
				cell5.setCellValue(fifoReportDTO.getAssignmentId());
			if(null != fifoReportDTO.getTransactionDate())
				cell6.setCellValue(simpleDateFormat.format(fifoReportDTO.getTransactionDate()));
			if(null != fifoReportDTO.getInvoiceNo())
				cell7.setCellValue(fifoReportDTO.getInvoiceNo());
			if(null != fifoReportDTO.getStrInvoiceDate())
				cell8.setCellValue(fifoReportDTO.getStrInvoiceDate());
			if(null != fifoReportDTO.getValueFCY())
				cell9.setCellValue(fifoReportDTO.getValueFCY());
			if(null != fifoReportDTO.getUnitFCY())
				cell10.setCellValue(fifoReportDTO.getUnitFCY());
			if(null != fifoReportDTO.getPurchaseType())
				cell11.setCellValue(fifoReportDTO.getPurchaseType());
			if(null != fifoReportDTO.getTransactionType())
				cell12.setCellValue(fifoReportDTO.getTransactionType());
			if(null != fifoReportDTO.getAcctType())
				cell13.setCellValue(fifoReportDTO.getAcctType());
			if(null != fifoReportDTO.getQuantity())
				cell14.setCellValue(fifoReportDTO.getQuantity());
			if(null != fifoReportDTO.getRate())
				cell15.setCellValue(fifoReportDTO.getRate());
			if(null != fifoReportDTO.getValue())
				cell16.setCellValue(fifoReportDTO.getValue());
			
			excelRowCounter++;
		}
		//logger.debug("Vendor Wise Sheet Appended");
		return excelRowCounter;
	}

	public static Integer appendDetailedSheet(Integer excelRowCounter, Workbook workbook, List<FIFOReportDTO> fifoReportDTOList, ReportGenerateForm reportForm,
			Date date2) throws IOException {

		String sheetName = reportForm.getReportCategory()+ reportForm.getReportType();
		int initialCounter = excelRowCounter;
		
		if(workbook.getNumberOfSheets() > 1){
			sheetName = reportForm.getReportCategory()+ reportForm.getReportType() + "_" + (workbook.getNumberOfSheets()-1);
		}else{
			sheetName = reportForm.getReportCategory()+ reportForm.getReportType(); 
		}
		
		Sheet sheet = workbook.getSheet(sheetName);

		for(FIFOReportDTO fifoReportDTO : fifoReportDTOList){
			if(excelRowCounter > 50000){ 
				sheetName = reportForm.getReportCategory()+ reportForm.getReportType() + "_" + workbook.getNumberOfSheets();
				workbook.createSheet(sheetName);
				createDetailedSheetHeaders(workbook, sheetName, reportForm.getCompanyName(), reportForm.getInvType(), reportForm.getValuationMethod(), reportForm.getStartDate(), reportForm.getEndDate());
				excelRowCounter = 10;
				sheet = workbook.getSheet(sheetName);
			}
		
		Row row = sheet.createRow(excelRowCounter);
		Cell cell0 = row.createCell(0);
		Cell cell1 = row.createCell(1);
		Cell cell2 = row.createCell(2);
		Cell cell3 = row.createCell(3);
		Cell cell4 = row.createCell(4);
		Cell cell5 = row.createCell(5);
		Cell cell6 = row.createCell(6);
		Cell cell7 = row.createCell(7);
		Cell cell8 = row.createCell(8);
		Cell cell9 = row.createCell(9);
		Cell cell10 = row.createCell(10);
		Cell cell11 = row.createCell(11);
		Cell cell12 = row.createCell(12);
		Cell cell13 = row.createCell(13);
		Cell cell14 = row.createCell(14);
		Cell cell15 = row.createCell(15);
		Cell cell16 = row.createCell(16);
		Cell cell17 = row.createCell(17);
		Cell cell18 = row.createCell(18);
		Cell cell19 = row.createCell(19);
		Cell cell20 = row.createCell(20);
		Cell cell21 = row.createCell(21);
		Cell cell22 = row.createCell(22);
		Cell cell23 = row.createCell(23);
		Cell cell24 = row.createCell(24);
		
		if(fifoReportDTO.getInventoryType()!=null)
			cell0.setCellValue(fifoReportDTO.getInventoryType());
		
		if(fifoReportDTO.getPartId()!=null)
			cell1.setCellValue(fifoReportDTO.getPartId());
		
		if(fifoReportDTO.getPartDesc()!=null)
			cell2.setCellValue(fifoReportDTO.getPartDesc());
		
		if(fifoReportDTO.getSapDocNo()!=null)
			cell3.setCellValue(fifoReportDTO.getSapDocNo());
		
		if(fifoReportDTO.getSapEntryDate()!=null)
			cell4.setCellValue(fifoReportDTO.getSapEntryDate());
		
		if(fifoReportDTO.getAssignmentId()!=null)
			cell5.setCellValue(fifoReportDTO.getAssignmentId());
		
		if(fifoReportDTO.getTransactionDate()!=null)
			cell6.setCellValue(fifoReportDTO.getTransactionDate());
		
		if(fifoReportDTO.getInvoiceNo()!=null)
			cell7.setCellValue(fifoReportDTO.getInvoiceNo());
		
		if(fifoReportDTO.getStrInvoiceDate()!=null)
			cell8.setCellValue(fifoReportDTO.getStrInvoiceDate());
		
		if(fifoReportDTO.getVendorCode()!=null)
			cell9.setCellValue(fifoReportDTO.getVendorCode());
		
		if(fifoReportDTO.getVendorName()!=null)
			cell10.setCellValue(fifoReportDTO.getVendorName());
		
		if(fifoReportDTO.getValueFCY()!=null)
			cell11.setCellValue(fifoReportDTO.getValueFCY());
		
		if(fifoReportDTO.getUnitFCY()!=null)
			cell12.setCellValue(fifoReportDTO.getUnitFCY());
		
		if(fifoReportDTO.getPurchaseType()!=null)
			cell13.setCellValue(fifoReportDTO.getPurchaseType());
		
		if(fifoReportDTO.getTransactionType()!=null)
			cell14.setCellValue(fifoReportDTO.getTransactionType());
		
		if(fifoReportDTO.getAcctType()!=null)
			cell15.setCellValue(fifoReportDTO.getAcctType());
		
		
		if(fifoReportDTO.getQuantity()!=null && FIFOReportGenerationService.Receipt_Transaction_Type.contains(fifoReportDTO.getTransactionType()))
			cell16.setCellValue(fifoReportDTO.getQuantity());
				
		if(fifoReportDTO.getRate()!=null && FIFOReportGenerationService.Receipt_Transaction_Type.contains(fifoReportDTO.getTransactionType()))
			cell17.setCellValue(fifoReportDTO.getRate());
		
		if(fifoReportDTO.getValue()!=null && FIFOReportGenerationService.Receipt_Transaction_Type.contains(fifoReportDTO.getTransactionType()))
			cell18.setCellValue(fifoReportDTO.getValue());
		
		if(fifoReportDTO.getQuantity()!=null && FIFOReportGenerationService.Consumption_Transaction_Type.contains(fifoReportDTO.getTransactionType()))
			cell19.setCellValue(fifoReportDTO.getQuantity());
		
		if(fifoReportDTO.getRate()!=null && FIFOReportGenerationService.Consumption_Transaction_Type.contains(fifoReportDTO.getTransactionType()))
			cell20.setCellValue(fifoReportDTO.getRate());
		
		if(fifoReportDTO.getValue()!=null && FIFOReportGenerationService.Consumption_Transaction_Type.contains(fifoReportDTO.getTransactionType()))
			cell21.setCellValue(fifoReportDTO.getValue());
		
		if(fifoReportDTO.printClosingStats 
				&& reportForm.getPurchaseType().equalsIgnoreCase("All")
				&& reportForm.getAccountType().equalsIgnoreCase("All") 
				&& reportForm.getTransactionType().equalsIgnoreCase("All")){
			if(fifoReportDTO.getClosingQuantity()!=null)
				cell22.setCellValue(fifoReportDTO.getClosingQuantity());
			
			if(fifoReportDTO.getClosingRate()!=null)
				cell23.setCellValue(fifoReportDTO.getClosingRate());
			
			if(fifoReportDTO.getClosingValue()!=null)
				cell24.setCellValue(fifoReportDTO.getClosingValue());
		}
		excelRowCounter++;
		}
		if(initialCounter != excelRowCounter)
			excelRowCounter++;
				

		//logger.debug("Detailed Sheet Appended");
		return excelRowCounter;
		
	}

	public static Integer createInvSummary(ArrayList<SummaryInv> invs, Workbook workbook, Integer excelsummpartCounter, ReportGenerateForm reportForm,Date date2) {
		
//		FileOutputStream fileOut =null;
		int initialCounter = excelsummpartCounter;
		try{
			String sheetName = "StockLedgerSummary-InventoryTypeWise";
			
			if(excelsummpartCounter > 50000){
				workbook.getNumberOfSheets();
				sheetName = sheetName + "_" + workbook.getNumberOfSheets();
				workbook.createSheet(sheetName);
				createInvTypeWiseSheetHeaders(workbook, sheetName, reportForm.getCompanyName(), reportForm.getValuationMethod(), reportForm.getStartDate(), reportForm.getEndDate());
				excelsummpartCounter = 10;
			}else if(workbook.getNumberOfSheets() > 1){
				sheetName = sheetName + "_" + (workbook.getNumberOfSheets()-1);
				createInvTypeWiseSheetHeaders(workbook, sheetName, reportForm.getCompanyName(), reportForm.getValuationMethod(), reportForm.getStartDate(), reportForm.getEndDate());
			}
			
			Sheet sheet = workbook.getSheet(sheetName);
				
			Row row = null;
			Cell cell0,cell1,cell2,cell3,cell4,cell5,cell6,cell7,cell8,cell9,cell10,cell11,cell12,cell13,cell14,cell15,cell16,cell17,cell18,cell19,cell20
			,cell21,cell22,cell23,cell24,cell25,cell26,cell27,cell28,cell29,cell30,cell31,cell32,cell33,cell34,cell35;
			
			long totalGRIRQuantity=0;
			long totalOpeningQuantity=0;
			long totalcustomQuantitty=0;
			long totalCostpriceQuantity=0;
			long totalInvAdjQuanitity=0;
			long totalForexQuantity=0;
			long totalFiAdjQuantity=0;
			long totalRetroQuantity=0;
			long totalConsQuantity=0;
			long totalFOCQuantity=0;
			long totalSalesQuantity=0;
			long totalRTVQuantity=0;
			long totalScrapQuantity=0;
			long totalCaptiveQuantity=0;
			long totalOtherQuantity=0;
			long totalClosingQuantity=0;
			double totalGRIRAmount=0;
			double totalOpeningAmount=0;
			double totalCustomAmount=0;
			double totalCostPriceAmount=0;
			double totalInvAdjAmount=0;
			double totalForexAmount=0;
			double totalFiAdjAmount=0;
			double totalRetroAmount=0;
			double totalConsAmount=0;
			double totalFOCAmount=0;
			double totalSalesAmount=0;
			double totalRTVAmount=0;
			double totalScrapAmount=0;
			double totalCaptiveAmount=0;
			double totalOtherAmount=0;
			double totalClosingAmount=0;
			double totalRate=0;
			for (SummaryInv inv : invs) {
				row=sheet.createRow(excelsummpartCounter);
				 cell0 = row.createCell(0);
				cell1 = row.createCell(1);
				 cell2 = row.createCell(2);
				 cell3 = row.createCell(3);
				 cell4 = row.createCell(4);
				 cell5 = row.createCell(5);
				 cell6 = row.createCell(6);
				 cell7 = row.createCell(7);
				 cell8 = row.createCell(8);
				 cell9 = row.createCell(9);
				 cell10 = row.createCell(10);
				cell11 = row.createCell(11);
				 cell12 = row.createCell(12);
				cell13 = row.createCell(13);
				 cell14 = row.createCell(14);
				 cell15 = row.createCell(15);
				 cell16 = row.createCell(16);
				 cell17 = row.createCell(17);
				 cell18 = row.createCell(18);
				 cell19 = row.createCell(19);
				 cell20 = row.createCell(20);
				 cell21 = row.createCell(21);
				 cell22 = row.createCell(22);
				 cell23 = row.createCell(23);
				 cell24 = row.createCell(24);
				 cell25 = row.createCell(25);
				 cell26 = row.createCell(26);
				 cell27 = row.createCell(27);
				 cell28 = row.createCell(28);
				 cell29 = row.createCell(29);
				 cell30 = row.createCell(30);
				 cell31 = row.createCell(31);
				cell32 = row.createCell(32);
				cell33 = row.createCell(33);
				cell34 = row.createCell(34);
				cell35 = row.createCell(35);
				
				//leaving cell1 and cell2
				
				cell0.setCellValue(inv.getInventoryType());
				cell3.setCellValue(inv.getOpeningQuanitity());totalOpeningQuantity+=inv.getOpeningQuanitity();
				cell4.setCellValue(inv.getOpeningAmount());totalOpeningAmount+=inv.getOpeningAmount();
				cell5.setCellValue(inv.getzGrirQuantity());totalGRIRQuantity+=inv.getzGrirQuantity();
				cell6.setCellValue(inv.getzGrirAmount());totalGRIRAmount+=inv.getzGrirAmount();
				cell7.setCellValue(inv.getCustomQuantity());totalcustomQuantitty+=inv.getCustomQuantity();
				cell8.setCellValue(inv.getCustomAmount());totalCustomAmount+=inv.getCustomAmount();
				cell9.setCellValue(inv.getCostPriceQuantity());totalCostpriceQuantity+=inv.getCostPriceQuantity();
				cell10.setCellValue(inv.getCostPriceAmount());totalCostPriceAmount+=inv.getCostPriceAmount();
				cell11.setCellValue(inv.getInvAdjQuantity());totalInvAdjQuanitity+=inv.getInvAdjQuantity();
				cell12.setCellValue(inv.getInvAdjAmount());totalInvAdjAmount+=inv.getInvAdjAmount();
				cell13.setCellValue(inv.getForexQuantity());totalForexQuantity+=inv.getForexQuantity();
				cell14.setCellValue(inv.getForexAmount());totalForexAmount+=inv.getForexAmount();
				cell15.setCellValue(inv.getFiAdjQuantity());totalFiAdjQuantity+=inv.getFiAdjQuantity();
				cell16.setCellValue(inv.getFiAdjAmount());totalFiAdjAmount+=inv.getFiAdjAmount();
				cell17.setCellValue(inv.getRetroQuantity());totalRetroQuantity+=inv.getRetroQuantity();
				cell18.setCellValue(inv.getRetroAmount());totalRetroAmount+=inv.getRetroAmount();
				
				cell19.setCellValue(inv.getSalesQuantity());totalSalesQuantity+=inv.getSalesQuantity();
				cell20.setCellValue(inv.getSalesAmount());totalSalesAmount+=inv.getSalesAmount();
				
				cell21.setCellValue(inv.getConsQuantity());totalConsQuantity+=inv.getConsQuantity();
				cell22.setCellValue(inv.getConsAmount());totalConsAmount+=inv.getConsAmount();
				cell23.setCellValue(inv.getFocQuantity());totalFOCQuantity+=inv.getFocQuantity();
				cell24.setCellValue(inv.getFocAmount());totalFOCAmount+=inv.getFocAmount();
				cell25.setCellValue(inv.getRtvQuantity());totalRTVQuantity+=inv.getRtvQuantity();
				cell26.setCellValue(inv.getRtvAmount());totalRTVAmount+=inv.getRtvAmount();
				cell27.setCellValue(inv.getScrapQuantity());totalScrapQuantity+=inv.getScrapQuantity();
				cell28.setCellValue(inv.getScrapAmount());totalScrapAmount+=inv.getScrapAmount();
				cell29.setCellValue(inv.getCaptiveQuantity());totalCaptiveQuantity+=inv.getCaptiveQuantity();
				cell30.setCellValue(inv.getCaptiveAmount());totalCaptiveAmount+=inv.getCaptiveAmount();
				cell31.setCellValue(inv.getOtherConsQuantity());totalOtherQuantity+=inv.getOtherConsQuantity();
				cell32.setCellValue(inv.getOtherConsAmount());totalOtherAmount+=inv.getOtherConsAmount();
				if(reportForm.getTransactionType().equalsIgnoreCase("All")
						&& reportForm.getAccountType().equalsIgnoreCase("All")
						&& reportForm.getPurchaseType().equalsIgnoreCase("All")){
					cell33.setCellValue(inv.getClosingQuantity());
					cell34.setCellValue(inv.getClosingRate());
					cell35.setCellValue(inv.getClosingValue());
				}
				totalClosingQuantity+=inv.getClosingQuantity();
				totalClosingAmount+=inv.getClosingValue();
				
				totalRate=totalClosingQuantity>0?(totalClosingAmount/totalClosingQuantity):0;
				//cell34.setCellValue(arg0);
				//cell35.setCellValue(arg0);
				excelsummpartCounter++;
			}
			row=sheet.createRow(excelsummpartCounter);
			cell0 = row.createCell(0);
			cell1 = row.createCell(1);
			 cell2 = row.createCell(2);
			 cell3 = row.createCell(3);
			 cell4 = row.createCell(4);
			 cell5 = row.createCell(5);
			 cell6 = row.createCell(6);
			 cell7 = row.createCell(7);
			 cell8 = row.createCell(8);
			 cell9 = row.createCell(9);
			 cell10 = row.createCell(10);
			cell11 = row.createCell(11);
			 cell12 = row.createCell(12);
			cell13 = row.createCell(13);
			 cell14 = row.createCell(14);
			 cell15 = row.createCell(15);
			 cell16 = row.createCell(16);
			 cell17 = row.createCell(17);
			 cell18 = row.createCell(18);
			 cell19 = row.createCell(19);
			 cell20 = row.createCell(20);
			 cell21 = row.createCell(21);
			 cell22 = row.createCell(22);
			 cell23 = row.createCell(23);
			 cell24 = row.createCell(24);
			 cell25 = row.createCell(25);
			 cell26 = row.createCell(26);
			 cell27 = row.createCell(27);
			 cell28 = row.createCell(28);
			 cell29 = row.createCell(29);
			 cell30 = row.createCell(30);
			 cell31 = row.createCell(31);
			cell32 = row.createCell(32);
			cell33 = row.createCell(33);
			cell34 = row.createCell(34);
			cell35 = row.createCell(35);
			
			cell0.setCellValue("Total");
			cell3.setCellValue(totalOpeningQuantity);
			cell4.setCellValue(totalOpeningAmount);
			cell5.setCellValue(totalGRIRQuantity);
			cell6.setCellValue(totalGRIRAmount);
			cell7.setCellValue(totalcustomQuantitty);
			cell8.setCellValue(totalCustomAmount);
			cell9.setCellValue(totalCostpriceQuantity);
			cell10.setCellValue(totalCostPriceAmount);
			cell11.setCellValue(totalInvAdjQuanitity);
			cell12.setCellValue(totalInvAdjAmount);
			cell13.setCellValue(totalForexQuantity);
			cell14.setCellValue(totalForexAmount);
			cell15.setCellValue(totalFiAdjQuantity);
			cell16.setCellValue(totalFiAdjAmount);
			cell17.setCellValue(totalRetroQuantity);
			cell18.setCellValue(totalRetroAmount);
			
			cell19.setCellValue(totalSalesQuantity);
			cell20.setCellValue(totalSalesAmount);
			
			cell21.setCellValue(totalConsQuantity);
			cell22.setCellValue(totalConsAmount);
			cell23.setCellValue(totalFOCQuantity);
			cell24.setCellValue(totalFOCAmount);
			cell25.setCellValue(totalRTVQuantity);
			cell26.setCellValue(totalRTVAmount);
			cell27.setCellValue(totalScrapQuantity);
			cell28.setCellValue(totalScrapAmount);
			cell29.setCellValue(totalCaptiveQuantity);
			cell30.setCellValue(totalCaptiveAmount);
			cell31.setCellValue(totalOtherQuantity);
			cell32.setCellValue(totalOtherAmount);
			if(reportForm.getTransactionType().equalsIgnoreCase("All")
					&& reportForm.getAccountType().equalsIgnoreCase("All")
					&& reportForm.getPurchaseType().equalsIgnoreCase("All")){
				cell33.setCellValue(totalClosingQuantity);
				cell34.setCellValue(totalRate);
				cell35.setCellValue(totalClosingAmount);
			}
		
			/* added by ashish */
			CellStyle cs6 = workbook.createCellStyle();
			cs6.setFillForegroundColor(IndexedColors.GREEN.getIndex());
			cs6.setFillPattern(CellStyle.SOLID_FOREGROUND);
			CellStyle cs9 = workbook.createCellStyle();
			cs9.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
			cs9.setFillPattern(CellStyle.SOLID_FOREGROUND);
			
			cell0.setCellStyle(cs6);
			cell1.setCellStyle(cs6);
			cell2.setCellStyle(cs6);
			cell3.setCellStyle(cs9);
			cell4.setCellStyle(cs9);
			cell5.setCellStyle(cs9);
			cell6.setCellStyle(cs9);
			cell7.setCellStyle(cs9);
			cell8.setCellStyle(cs9);
			cell9.setCellStyle(cs9);
			cell10.setCellStyle(cs9);
			cell11.setCellStyle(cs9);
			cell12.setCellStyle(cs9);
			cell13.setCellStyle(cs9);
			cell14.setCellStyle(cs9);
			cell15.setCellStyle(cs9);
			cell16.setCellStyle(cs9);
			cell17.setCellStyle(cs9);
			cell18.setCellStyle(cs9);
			cell19.setCellStyle(cs9);
			cell20.setCellStyle(cs9);
			cell21.setCellStyle(cs9);
			cell22.setCellStyle(cs9);
			cell23.setCellStyle(cs9);
			cell24.setCellStyle(cs9);
			cell25.setCellStyle(cs9);
			cell26.setCellStyle(cs9);
			cell27.setCellStyle(cs9);
			cell28.setCellStyle(cs9);
			cell29.setCellStyle(cs9);
			cell30.setCellStyle(cs9);
			cell31.setCellStyle(cs9);
			cell32.setCellStyle(cs9);
			cell33.setCellStyle(cs9);
			cell34.setCellStyle(cs9);
			cell35.setCellStyle(cs9);
			/*    */
					
		}
		catch (Exception e) {
			e.printStackTrace();
		}
			
		if(initialCounter != excelsummpartCounter)
			excelsummpartCounter++;
		

		//logger.debug("Inv Type Wise Sheet Appended");
		
		return excelsummpartCounter;	
		
	}

	public static Integer createPartSummary(ArrayList<SummaryParts> summaryParts, Workbook workbook, Integer excelsummpartCounter, ReportGenerateForm reportForm,Date date2) {
		
		int initialCounter = excelsummpartCounter;
		try{
			String sheetName = "StockLedgerSummary-PartWise";
			
			if(excelsummpartCounter > 50000){
				workbook.getNumberOfSheets();
				sheetName = sheetName + "_" + workbook.getNumberOfSheets();
				workbook.createSheet(sheetName);
				createPartWiseSheetHeaders(workbook, sheetName, reportForm.getCompanyName(), reportForm.getValuationMethod(), reportForm.getStartDate(), reportForm.getEndDate());
				excelsummpartCounter = 10;
			}else if(workbook.getNumberOfSheets() > 1){
				sheetName = sheetName + "_" + (workbook.getNumberOfSheets()-1);
				createPartWiseSheetHeaders(workbook, sheetName, reportForm.getCompanyName(), reportForm.getValuationMethod(), reportForm.getStartDate(), reportForm.getEndDate());
			}
			
			Sheet sheet = workbook.getSheet(sheetName);

			long totalGRIRQuantity=0;
			long totalOpeningQuantity=0;
			long totalcustomQuantitty=0;
			long totalCostpriceQuantity=0;
			long totalInvAdjQuanitity=0;
			long totalForexQuantity=0;
			long totalFiAdjQuantity=0;
			long totalRetroQuantity=0;
			long totalConsQuantity=0;
			long totalFOCQuantity=0;
			long totalSalesQuantity=0;
			long totalRTVQuantity=0;
			long totalScrapQuantity=0;
			long totalCaptiveQuantity=0;
			long totalOtherQuantity=0;
			long totalClosingQuantity=0;
			double totalGRIRAmount=0;
			double totalOpeningAmount=0;
			double totalCustomAmount=0;
			double totalCostPriceAmount=0;
			double totalInvAdjAmount=0;
			double totalForexAmount=0;
			double totalFiAdjAmount=0;
			double totalRetroAmount=0;
			double totalConsAmount=0;
			double totalFOCAmount=0;
			double totalSalesAmount=0;
			double totalRTVAmount=0;
			double totalScrapAmount=0;
			double totalCaptiveAmount=0;
			double totalOtherAmount=0;
			double totalClosingAmount=0;
			double totalRate=0;
			
			Row row = null;
			Cell cell0,cell1,cell2,cell3,cell4,cell5,cell6,cell7,cell8,cell9,cell10,cell11,cell12,cell13,cell14,cell15,cell16,cell17,cell18,cell19,cell20
			,cell21,cell22,cell23,cell24,cell25,cell26,cell27,cell28,cell29,cell30,cell31,cell32,cell33,cell34,cell35;
			
			for (SummaryParts parts : summaryParts) {
				row=sheet.createRow(excelsummpartCounter);
				 cell0 = row.createCell(0);
				cell1 = row.createCell(1);
				 cell2 = row.createCell(2);
				 cell3 = row.createCell(3);
				 cell4 = row.createCell(4);
				 cell5 = row.createCell(5);
				 cell6 = row.createCell(6);
				 cell7 = row.createCell(7);
				 cell8 = row.createCell(8);
				 cell9 = row.createCell(9);
				 cell10 = row.createCell(10);
				cell11 = row.createCell(11);
				 cell12 = row.createCell(12);
				cell13 = row.createCell(13);
				 cell14 = row.createCell(14);
				 cell15 = row.createCell(15);
				 cell16 = row.createCell(16);
				 cell17 = row.createCell(17);
				 cell18 = row.createCell(18);
				 cell19 = row.createCell(19);
				 cell20 = row.createCell(20);
				 cell21 = row.createCell(21);
				 cell22 = row.createCell(22);
				 cell23 = row.createCell(23);
				 cell24 = row.createCell(24);
				 cell25 = row.createCell(25);
				 cell26 = row.createCell(26);
				 cell27 = row.createCell(27);
				 cell28 = row.createCell(28);
				 cell29 = row.createCell(29);
				 cell30 = row.createCell(30);
				 cell31 = row.createCell(31);
				cell32 = row.createCell(32);
				cell33 = row.createCell(33);
				cell34 = row.createCell(34);
				cell35 = row.createCell(35);
				
				//leaving cell1 and cell2
				
				cell0.setCellValue(parts.getInventoryType());
				cell1.setCellValue(parts.getPartId());
				cell2.setCellValue(parts.getPartDesc());
				cell3.setCellValue(parts.getOpeningQuanitity());totalOpeningQuantity+=parts.getOpeningQuanitity();
				cell4.setCellValue(parts.getOpeningAmount());totalOpeningAmount+=parts.getOpeningAmount();
				cell5.setCellValue(parts.getzGrirQuantity());totalGRIRQuantity+=parts.getzGrirQuantity();
				cell6.setCellValue(parts.getzGrirAmount());totalGRIRAmount+=parts.getzGrirAmount();
				cell7.setCellValue(parts.getCustomQuantity());totalcustomQuantitty+=parts.getCustomQuantity();
				cell8.setCellValue(parts.getCustomAmount());totalCustomAmount+=parts.getCustomAmount();
				cell9.setCellValue(parts.getCostPriceQuantity());totalCostpriceQuantity+=parts.getCostPriceQuantity();
				cell10.setCellValue(parts.getCostPriceAmount());totalCostPriceAmount+=parts.getCostPriceAmount();
				cell11.setCellValue(parts.getInvAdjQuantity());totalInvAdjQuanitity+=parts.getInvAdjQuantity();
				cell12.setCellValue(parts.getInvAdjAmount());totalInvAdjAmount+=parts.getInvAdjAmount();
				cell13.setCellValue(parts.getForexQuantity());totalForexQuantity+=parts.getForexQuantity();
				cell14.setCellValue(parts.getForexAmount());totalForexAmount+=parts.getForexAmount();
				cell15.setCellValue(parts.getFiAdjQuantity());totalFiAdjQuantity+=parts.getFiAdjQuantity();
				cell16.setCellValue(parts.getFiAdjAmount());totalFiAdjAmount+=parts.getFiAdjAmount();
				cell17.setCellValue(parts.getRetroQuantity());totalRetroQuantity+=parts.getRetroQuantity();
				cell18.setCellValue(parts.getRetroAmount());totalRetroAmount+=parts.getRetroAmount();
				
				cell19.setCellValue(parts.getSalesQuantity());totalSalesQuantity+=parts.getSalesQuantity();
				cell20.setCellValue(parts.getSalesAmount());totalSalesAmount+=parts.getSalesAmount();
				
				cell21.setCellValue(parts.getConsQuantity());totalConsQuantity+=parts.getConsQuantity();
				cell22.setCellValue(parts.getConsAmount());totalConsAmount+=parts.getConsAmount();
				cell23.setCellValue(parts.getFocQuantity());totalFOCQuantity+=parts.getFocQuantity();
				cell24.setCellValue(parts.getFocAmount());totalFOCAmount+=parts.getFocAmount();
				cell25.setCellValue(parts.getRtvQuantity());totalRTVQuantity+=parts.getRtvQuantity();
				cell26.setCellValue(parts.getRtvAmount());totalRTVAmount+=parts.getRtvAmount();
				cell27.setCellValue(parts.getScrapQuantity());totalScrapQuantity+=parts.getScrapQuantity();
				cell28.setCellValue(parts.getScrapAmount());totalScrapAmount+=parts.getScrapAmount();
				cell29.setCellValue(parts.getCaptiveQuantity());totalCaptiveQuantity+=parts.getCaptiveQuantity();
				cell30.setCellValue(parts.getCaptiveAmount());totalCaptiveAmount+=parts.getCaptiveAmount();
				cell31.setCellValue(parts.getOtherConsQuantity());totalOtherQuantity+=parts.getOtherConsQuantity();
				cell32.setCellValue(parts.getOtherConsAmount());totalOtherAmount+=parts.getOtherConsAmount();
				if(reportForm.getTransactionType().equalsIgnoreCase("All")
						&& reportForm.getAccountType().equalsIgnoreCase("All")
						&& reportForm.getPurchaseType().equalsIgnoreCase("All")){
					cell33.setCellValue(parts.getClosingQuantity());
					cell34.setCellValue(parts.getClosingRate());
					cell35.setCellValue(parts.getClosingValue());
				}
				
				totalClosingQuantity+=parts.getClosingQuantity();
				totalClosingAmount+=parts.getClosingValue();
				totalRate=totalClosingQuantity>0?(totalClosingAmount/totalClosingQuantity):0;
				//cell34.setCellValue(arg0);
				//cell35.setCellValue(arg0);
				excelsummpartCounter++;
				
			}
			row=sheet.createRow(excelsummpartCounter);
			cell0 = row.createCell(0);
			cell1 = row.createCell(1);
			 cell2 = row.createCell(2);
			 cell3 = row.createCell(3);
			 cell4 = row.createCell(4);
			 cell5 = row.createCell(5);
			 cell6 = row.createCell(6);
			 cell7 = row.createCell(7);
			 cell8 = row.createCell(8);
			 cell9 = row.createCell(9);
			 cell10 = row.createCell(10);
			cell11 = row.createCell(11);
			 cell12 = row.createCell(12);
			cell13 = row.createCell(13);
			 cell14 = row.createCell(14);
			 cell15 = row.createCell(15);
			 cell16 = row.createCell(16);
			 cell17 = row.createCell(17);
			 cell18 = row.createCell(18);
			 cell19 = row.createCell(19);
			 cell20 = row.createCell(20);
			 cell21 = row.createCell(21);
			 cell22 = row.createCell(22);
			 cell23 = row.createCell(23);
			 cell24 = row.createCell(24);
			 cell25 = row.createCell(25);
			 cell26 = row.createCell(26);
			 cell27 = row.createCell(27);
			 cell28 = row.createCell(28);
			 cell29 = row.createCell(29);
			 cell30 = row.createCell(30);
			 cell31 = row.createCell(31);
			cell32 = row.createCell(32);
			cell33 = row.createCell(33);
			cell34 = row.createCell(34);
			cell35 = row.createCell(35);
			
			cell0.setCellValue("Total");
			cell3.setCellValue(totalOpeningQuantity);
			cell4.setCellValue(totalOpeningAmount);
			cell5.setCellValue(totalGRIRQuantity);
			cell6.setCellValue(totalGRIRAmount);
			cell7.setCellValue(totalcustomQuantitty);
			cell8.setCellValue(totalCustomAmount);
			cell9.setCellValue(totalCostpriceQuantity);
			cell10.setCellValue(totalCostPriceAmount);
			cell11.setCellValue(totalInvAdjQuanitity);
			cell12.setCellValue(totalInvAdjAmount);
			cell13.setCellValue(totalForexQuantity);
			cell14.setCellValue(totalForexAmount);
			cell15.setCellValue(totalFiAdjQuantity);
			cell16.setCellValue(totalFiAdjAmount);
			cell17.setCellValue(totalRetroQuantity);
			cell18.setCellValue(totalRetroAmount);
			
			cell19.setCellValue(totalSalesQuantity);
			cell20.setCellValue(totalSalesAmount);
			
			cell21.setCellValue(totalConsQuantity);
			cell22.setCellValue(totalConsAmount);
			cell23.setCellValue(totalFOCQuantity);
			cell24.setCellValue(totalFOCAmount);
			cell25.setCellValue(totalRTVQuantity);
			cell26.setCellValue(totalRTVAmount);
			cell27.setCellValue(totalScrapQuantity);
			cell28.setCellValue(totalScrapAmount);
			cell29.setCellValue(totalCaptiveQuantity);
			cell30.setCellValue(totalCaptiveAmount);
			cell31.setCellValue(totalOtherQuantity);
			cell32.setCellValue(totalOtherAmount);
			if(reportForm.getTransactionType().equalsIgnoreCase("All")
					&& reportForm.getAccountType().equalsIgnoreCase("All")
					&& reportForm.getPurchaseType().equalsIgnoreCase("All")){
				cell33.setCellValue(totalClosingQuantity);
				cell34.setCellValue(totalRate);
				cell35.setCellValue(totalClosingAmount);
			}
			
			/* added by ashish*/
			CellStyle cs6 = workbook.createCellStyle();
			cs6.setFillForegroundColor(IndexedColors.GREEN.getIndex());
			cs6.setFillPattern(CellStyle.SOLID_FOREGROUND);
			CellStyle cs9 = workbook.createCellStyle();
			cs9.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
			cs9.setFillPattern(CellStyle.SOLID_FOREGROUND);
			
			cell0.setCellStyle(cs6);
			cell1.setCellStyle(cs6);
			cell2.setCellStyle(cs6);
			cell3.setCellStyle(cs9);
			cell4.setCellStyle(cs9);
			cell5.setCellStyle(cs9);
			cell6.setCellStyle(cs9);
			cell7.setCellStyle(cs9);
			cell8.setCellStyle(cs9);
			cell9.setCellStyle(cs9);
			cell10.setCellStyle(cs9);
			cell11.setCellStyle(cs9);
			cell12.setCellStyle(cs9);
			cell13.setCellStyle(cs9);
			cell14.setCellStyle(cs9);
			cell15.setCellStyle(cs9);
			cell16.setCellStyle(cs9);
			cell17.setCellStyle(cs9);
			cell18.setCellStyle(cs9);
			cell19.setCellStyle(cs9);
			cell20.setCellStyle(cs9);
			cell21.setCellStyle(cs9);
			cell22.setCellStyle(cs9);
			cell23.setCellStyle(cs9);
			cell24.setCellStyle(cs9);
			cell25.setCellStyle(cs9);
			cell26.setCellStyle(cs9);
			cell27.setCellStyle(cs9);
			cell28.setCellStyle(cs9);
			cell29.setCellStyle(cs9);
			cell30.setCellStyle(cs9);
			cell31.setCellStyle(cs9);
			cell32.setCellStyle(cs9);
			cell33.setCellStyle(cs9);
			cell34.setCellStyle(cs9);
			cell35.setCellStyle(cs9);
			/*  */
			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		if(initialCounter != excelsummpartCounter)
			excelsummpartCounter++;
		

		//logger.debug("Part Wise Sheet Appended");
		return excelsummpartCounter;
	}
	
	public static void createDetailedSheetHeaders(Workbook wb, String sheetName, String companyName, String invType, String valuationMethod, Date startDate, Date endDate){
		
			Sheet sheet = wb.getSheet(sheetName);
			
			/* added by ashish */
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 10));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 24));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 11));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 14));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 15, 24));
			sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 24));
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 2));
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 3, 24));
			sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 1));
			sheet.addMergedRegion(new CellRangeAddress(4, 4, 2, 24));
			sheet.addMergedRegion(new CellRangeAddress(5, 5, 6, 24));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 6));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 7, 10));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 11, 15));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 17, 18));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 20, 21));
			sheet.addMergedRegion(new CellRangeAddress(7, 7, 22, 24));
			/*  */
			
			CellStyle cs1 = wb.createCellStyle();
			CellStyle cs2 = wb.createCellStyle();
			CellStyle cs3 = wb.createCellStyle();
			CellStyle cs4 = wb.createCellStyle();
			CellStyle cs5 = wb.createCellStyle();
			CellStyle cs6 = wb.createCellStyle();
			CellStyle cs7 = wb.createCellStyle();

			f1 = wb.createFont();
			f1.setColor(HSSFColor.DARK_BLUE.index);
			f1.setBoldweight(Font.BOLDWEIGHT_BOLD);

			Row row0 = sheet.createRow(0); // ROW 0
			row0.createCell(0).setCellValue("Company Name:");
			Cell cell3 = row0.createCell(3);
			if(companyName.equals("RNAIPL"))
				cell3.setCellValue("RENAULT NISSAN AUTOMOTIVE INDIA PVT LTD");
			else if(companyName.equals("NMIPL"))
				cell3.setCellValue("NISSAN MOTOR INDIA PVT LTD");
			cs1.setFont(f1);
			cell3.setCellStyle(cs1);

			Row row1 = sheet.createRow(1); // ROW 1
			Cell cell112 = row1.createCell(12);
			cell112.setCellValue("STORES LEDGER - DETAILED");
			cell112.setCellStyle(cs1);

			Row row2 = sheet.createRow(2); // ROW 2 (EMPTY for border)
			cs7.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);

			Row row3 = sheet.createRow(3); // ROW 3
			Cell cell30 = row3.createCell(0);
			cell30.setCellValue("STORES LEDGER FOR:"+invType);

			Row row4 = sheet.createRow(4); // ROW 4
			Cell cell40 = row4.createCell(0);
			cell40.setCellValue("STOCK VALUATION METHOD:"+valuationMethod);

			Row row5 = sheet.createRow(5); // ROW 5
			Cell cell50 = row5.createCell(0);
			Cell cell52 = row5.createCell(2);
			
			Cell dateCell1 = row5.createCell(3);
			Cell cell54 = row5.createCell(4);
			Cell dateCell2 = row5.createCell(5);
			cell50.setCellValue("PERIOD:");
			cell52.setCellValue("FROM:");
			
			
/*
			CreationHelper creationHelper = wb.getCreationHelper();
			creationHelper.createDataFormat().*/
			  dateCell1.setCellValue(startDate);
			  dateCell1.setCellType(Cell.CELL_TYPE_STRING);
			  dateCell2.setCellType(Cell.CELL_TYPE_STRING);
			  dateCell2.setCellValue(endDate);
			// CellStyle cellStyle3 = wb.createCellStyle();
			 
			 /*(creationHelper.createDataFormat().getFormat("dd/mm/yyyy"));
			 dateCell1.setCellStyle(cellStyle3);*/
			

			cell54.setCellValue("TO:");

			Row row6 = sheet.createRow(6); // ROW 6 (EMPTY for border)
			cs2.setBorderTop(HSSFBorderFormatting.BORDER_THICK);
			cs2.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);
			/* row6.setRowStyle(hcs2); */

			Row row7 = sheet.createRow(7); // ROW 7

			cs3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
			cs3.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs4.setFillForegroundColor(IndexedColors.PINK.getIndex());
			cs4.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs5.setFillForegroundColor(IndexedColors.AQUA.getIndex());
			cs5.setFillPattern(CellStyle.SOLID_FOREGROUND);
			cs6.setFillForegroundColor(IndexedColors.GREEN.getIndex());
			cs6.setFillPattern(CellStyle.SOLID_FOREGROUND);

			Cell cell77 = row7.createCell(7);
			Cell cell717 = row7.createCell(17);
			Cell cell720 = row7.createCell(20);
			Cell cell722 = row7.createCell(22);

			cell77.setCellValue("STORES LEDGER REFERENCE");
			cell717.setCellValue("PURCHASE/ RECEIPTS");
			cell720.setCellValue("SALE/ CONSUMPTION");
			cell722.setCellValue("CLOSING BOOK STOCK");

			cell77.setCellStyle(cs3);
			cell717.setCellStyle(cs4);
			cell720.setCellStyle(cs5);
			cell722.setCellStyle(cs6);

			row7.createCell(0).setCellStyle(cs3);
			row7.createCell(1).setCellStyle(cs3);
			row7.createCell(2).setCellStyle(cs3);
			row7.createCell(3).setCellStyle(cs3);
			row7.createCell(4).setCellStyle(cs3);
			row7.createCell(5).setCellStyle(cs3);
			row7.createCell(6).setCellStyle(cs3);
			row7.createCell(8).setCellStyle(cs3);
			row7.createCell(9).setCellStyle(cs3);
			row7.createCell(10).setCellStyle(cs3);
			row7.createCell(11).setCellStyle(cs3);
			row7.createCell(12).setCellStyle(cs3);
			row7.createCell(13).setCellStyle(cs3);
			row7.createCell(14).setCellStyle(cs3);
			row7.createCell(15).setCellStyle(cs3);
			row7.createCell(16).setCellStyle(cs4);
			row7.createCell(18).setCellStyle(cs4);
			row7.createCell(19).setCellStyle(cs5);
			row7.createCell(21).setCellStyle(cs5);
			row7.createCell(23).setCellStyle(cs6);
			row7.createCell(24).setCellStyle(cs6);
			

			Row row8 = sheet.createRow(8); // ROW 8

			Cell cell80 = row8.createCell(0);
			Cell cell81 = row8.createCell(1);
			Cell cell82 = row8.createCell(2);
			Cell cell83 = row8.createCell(3);
			Cell cell84 = row8.createCell(4);
			Cell cell85 = row8.createCell(5);
			Cell cell86 = row8.createCell(6);
			Cell cell87 = row8.createCell(7);
			Cell cell88 = row8.createCell(8);
			Cell cell89 = row8.createCell(9);
			Cell cell810 = row8.createCell(10);
			Cell cell811 = row8.createCell(11);
			Cell cell812 = row8.createCell(12);
			Cell cell813 = row8.createCell(13);
			Cell cell814 = row8.createCell(14);
			Cell cell815 = row8.createCell(15);
			Cell cell816 = row8.createCell(16);
			Cell cell817 = row8.createCell(17);
			Cell cell818 = row8.createCell(18);
			Cell cell819 = row8.createCell(19);
			Cell cell820 = row8.createCell(20);
			Cell cell821 = row8.createCell(21);
			Cell cell822 = row8.createCell(22);
			Cell cell823 = row8.createCell(23);
			Cell cell824 = row8.createCell(24);

			cell80.setCellValue("Inv Type");
			cell81.setCellValue("Material Code");
			cell82.setCellValue("Part Desc");
			cell83.setCellValue("SAP Doc No");
			cell84.setCellValue("Entry Date");
			cell85.setCellValue("Assignment No");
			cell86.setCellValue("Transaction Date");
			cell87.setCellValue("Vendor Invoice No");
			cell88.setCellValue("Invoice Date");
			cell89.setCellValue("Vendor Code");
			cell810.setCellValue("Vendor Name");
			cell811.setCellValue("Amount in FCY");
			cell812.setCellValue("FCY Unit");
			cell813.setCellValue("Purchase Type");
			cell814.setCellValue("Transaction Type");
			cell815.setCellValue("AcctType");
			cell816.setCellValue("Qty");
			cell817.setCellValue("Unit Rate");
			cell818.setCellValue(" Amount in Local Currency");
			cell819.setCellValue("Qty");
			cell820.setCellValue("Unit Rate");
			cell821.setCellValue(" Amount in Local Currency");
			cell822.setCellValue("Qty");
			cell823.setCellValue("Average Rate");
			cell824.setCellValue("Value");
			
			cell80.setCellStyle(cs3);
			cell81.setCellStyle(cs3);
			cell82.setCellStyle(cs3);
			cell83.setCellStyle(cs3);
			cell84.setCellStyle(cs3);
			cell85.setCellStyle(cs3);
			cell86.setCellStyle(cs3);
			cell87.setCellStyle(cs3);
			cell88.setCellStyle(cs3);
			cell89.setCellStyle(cs3);
			cell810.setCellStyle(cs3);
			cell811.setCellStyle(cs3);
			cell812.setCellStyle(cs3);
			cell813.setCellStyle(cs3);
			cell814.setCellStyle(cs3);
			cell815.setCellStyle(cs3);
			cell816.setCellStyle(cs4);
			cell817.setCellStyle(cs4);
			cell818.setCellStyle(cs4);
			cell819.setCellStyle(cs5);
			cell820.setCellStyle(cs5);
			cell821.setCellStyle(cs5);
			cell822.setCellStyle(cs6);
			cell823.setCellStyle(cs6);
			cell824.setCellStyle(cs6);
		
	}
	
	public static void createPartWiseSheetHeaders(Workbook wb, String sheetName, String companyName, String valuationMethod, Date strStartDate, Date strEndDate){

		Sheet sheet = wb.getSheet(sheetName);
		/* added by ashish */
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 10));
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 35));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 11));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 14));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 15, 35));
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 35));
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 3));
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 3, 35));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 6, 35));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 0, 2));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 3, 4));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 5, 10));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 11, 13));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 14, 18));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 19, 25));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 26, 28));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 29, 32));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 33, 35));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 2));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 3, 4));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 5, 6));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 7, 8));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 9, 10));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 11, 12));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 13, 14));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 15, 16));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 17, 18));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 19, 20));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 21, 22));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 23, 24));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 25, 26));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 27, 28));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 29, 30));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 31, 32));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 33, 35));
		/*  */ 
		
		CellStyle cs1 = wb.createCellStyle();
		CellStyle cs2 = wb.createCellStyle();
		CellStyle cs3 = wb.createCellStyle();
		CellStyle cs4 = wb.createCellStyle();
		CellStyle cs5 = wb.createCellStyle();
		CellStyle cs6 = wb.createCellStyle();
		CellStyle cs7 = wb.createCellStyle();
		CellStyle cs8 = wb.createCellStyle();
		CellStyle cs9 = wb.createCellStyle();

		f1 = wb.createFont();
		f1.setColor(HSSFColor.DARK_BLUE.index);
		f1.setBoldweight(Font.BOLDWEIGHT_BOLD);

		Row row0 = sheet.createRow(0); // ROW 0
		row0.createCell(0).setCellValue("Company Name:");
		Cell cell3 = row0.createCell(3);
		if(companyName.equals("RNAIPL"))
			cell3.setCellValue("RENAULT NISSAN AUTOMOTIVE INDIA PVT LTD");
		else if(companyName.equals("NMIPL"))
			cell3.setCellValue("NISSAN MOTOR INDIA PVT LTD");
		cs1.setFont(f1);
		cell3.setCellStyle(cs1);

		Row row1 = sheet.createRow(1); // ROW 1
		Cell cell112 = row1.createCell(12);
		cell112.setCellValue("STORES LEDGER - SUMMARY - PART WISE");
		cell112.setCellStyle(cs1);

		Row row2 = sheet.createRow(2); // ROW 2 (EMPTY for border)
		cs7.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);


		/*Row row3 = sheet.createRow(3); // ROW 3
		Cell cell30 = row3.createCell(0);
		cell30.setCellValue("STORES LEDGER FOR:");*/

		Row row3 = sheet.createRow(3); // ROW 3
		Cell cell30 = row3.createCell(0);
		cell30.setCellValue("STOCK VALUATION METHOD:"+valuationMethod);

		Row row4 = sheet.createRow(4); // ROW 4
		Cell cell40 = row4.createCell(0);
		Cell cell42 = row4.createCell(2);
		Cell dateCell1 = row4.createCell(3);
		Cell cell44 = row4.createCell(4);
		Cell dateCell2 = row4.createCell(5);
		cell40.setCellValue("PERIOD:");
		cell42.setCellValue("FROM:");
		
		 dateCell1.setCellValue(strStartDate);
		  dateCell1.setCellType(Cell.CELL_TYPE_STRING);
		  dateCell2.setCellType(Cell.CELL_TYPE_STRING);
		  dateCell2.setCellValue(strEndDate);
		/*
		 * Cell dateCell1 = row4.createCell(3); dateCell1.setCellValue(new
		 * Date()); CellStyle cellStyle3 = wb.createCellStyle();
		 * cellStyle3.setDataFormat
		 * (creationHelper.createDataFormat().getFormat("dd/mm/yyyy"));
		 * dateCell1.setCellStyle(cellStyle3);
		 */

		cell44.setCellValue("TO:");

		Row row5 = sheet.createRow(5); // ROW 5 (EMPTY for border)
		cs2.setBorderTop(HSSFBorderFormatting.BORDER_THICK);
		cs2.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);
		/* row5.setRowStyle(hcs2); */

		Row row6 = sheet.createRow(6); // ROW 6

		cs3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
		cs3.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs4.setFillForegroundColor(IndexedColors.PINK.getIndex());
		cs4.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs5.setFillForegroundColor(IndexedColors.AQUA.getIndex());
		cs5.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs6.setFillForegroundColor(IndexedColors.GREEN.getIndex());
		cs6.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs8.setFillForegroundColor(IndexedColors.RED.getIndex());
		cs8.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs9.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
		cs9.setFillPattern(CellStyle.SOLID_FOREGROUND);

		Cell cell60 = row6.createCell(0);
		Cell cell63 = row6.createCell(3);
		Cell cell611 = row6.createCell(11);
		Cell cell626 = row6.createCell(26);
		Cell cell633 = row6.createCell(33);

		cell60.setCellValue("STORES LEDGER REFERENCE");
		cell63.setCellValue("STOCK BALANCE");
		cell611.setCellValue("PURCHASE/ RECEIPTS");
		cell626.setCellValue("SALE/ CONSUMPTION");
		cell633.setCellValue("STOCK BALANCE");

		cell60.setCellStyle(cs3);
		cell63.setCellStyle(cs4);
		cell611.setCellStyle(cs5);
		cell626.setCellStyle(cs8);
		cell633.setCellStyle(cs6);
		
		row6.createCell(5).setCellStyle(cs5);
		row6.createCell(6).setCellStyle(cs5);
		row6.createCell(7).setCellStyle(cs5);
		row6.createCell(8).setCellStyle(cs5);
		row6.createCell(9).setCellStyle(cs5);
		row6.createCell(10).setCellStyle(cs5);
		row6.createCell(14).setCellStyle(cs5);
		row6.createCell(15).setCellStyle(cs5);
		row6.createCell(16).setCellStyle(cs5);
		row6.createCell(17).setCellStyle(cs5);
		row6.createCell(18).setCellStyle(cs5);
		row6.createCell(19).setCellStyle(cs8);
		row6.createCell(20).setCellStyle(cs8);
		row6.createCell(21).setCellStyle(cs8);
		row6.createCell(22).setCellStyle(cs8);
		row6.createCell(23).setCellStyle(cs8);
		row6.createCell(24).setCellStyle(cs8);
		row6.createCell(25).setCellStyle(cs8);
		row6.createCell(29).setCellStyle(cs8);
		row6.createCell(30).setCellStyle(cs8);
		row6.createCell(31).setCellStyle(cs8);
		row6.createCell(32).setCellStyle(cs8);
		
		
		Row row7 = sheet.createRow(7); // ROW 7
		
		row7.createCell(0).setCellStyle(cs3);
		row7.createCell(1).setCellStyle(cs3);
		row7.createCell(2).setCellStyle(cs3);
		
		Cell cell73 = row7.createCell(3);
		row7.createCell(4);
		Cell cell75 = row7.createCell(5);
		row7.createCell(6);
		Cell cell77 = row7.createCell(7);
		row7.createCell(8);
		Cell cell79 = row7.createCell(9);
		row7.createCell(10);
		Cell cell711 = row7.createCell(11);
		row7.createCell(12);
		Cell cell713 = row7.createCell(13);
		row7.createCell(14);
		Cell cell715 = row7.createCell(15);
		row7.createCell(16);
		Cell cell717 = row7.createCell(17);
		row7.createCell(18);
		Cell cell719 = row7.createCell(19);
		row7.createCell(20);
		Cell cell721 = row7.createCell(21);
		row7.createCell(22);
		Cell cell723 = row7.createCell(23);
		row7.createCell(24);
		Cell cell725 = row7.createCell(25);
		row7.createCell(26);
		Cell cell727 = row7.createCell(27);
		row7.createCell(28);
		Cell cell729 = row7.createCell(29);
		row7.createCell(30);
		Cell cell731 = row7.createCell(31);
		row7.createCell(32);
		Cell cell733 = row7.createCell(33);
		row7.createCell(34);
		row7.createCell(35);
		
		
		cell73.setCellValue("OPENING BOOK STOCK");
		cell75.setCellValue("ZGRIR");
		cell77.setCellValue("CUSTOM");
		cell79.setCellValue("COSTPRICE");
		cell711.setCellValue("INVADJ");
		cell713.setCellValue("FOREX");
		cell715.setCellValue("FI ADJ");
		cell717.setCellValue("RETRO");
		cell719.setCellValue("SALES");
		cell721.setCellValue("CONS");
		cell723.setCellValue("FOC");
		cell725.setCellValue("RTV");
		cell727.setCellValue("SCRAP");
		cell729.setCellValue("CAPTIVE");
		cell731.setCellValue("OTHER CONS");
		cell733.setCellValue("CLOSING BOOK STOCK");
		
		cell73.setCellStyle(cs4);
		cell75.setCellStyle(cs3);
		cell77.setCellStyle(cs3);
		cell79.setCellStyle(cs3);
		cell711.setCellStyle(cs3);
		cell713.setCellStyle(cs3);
		cell715.setCellStyle(cs3);
		cell717.setCellStyle(cs3);
		cell719.setCellStyle(cs9);
		cell721.setCellStyle(cs9);
		cell723.setCellStyle(cs9);
		cell723.setCellStyle(cs9);
		cell725.setCellStyle(cs9);
		cell727.setCellStyle(cs9);
		cell729.setCellStyle(cs9);
		cell731.setCellStyle(cs9);
		cell733.setCellStyle(cs6);
		
		
		Row row8 = sheet.createRow(8); // ROW 8

		Cell cell80 = row8.createCell(0);
		Cell cell81 = row8.createCell(1);
		Cell cell82 = row8.createCell(2);
		Cell cell83 = row8.createCell(3);
		Cell cell84 = row8.createCell(4);
		Cell cell85 = row8.createCell(5);
		Cell cell86 = row8.createCell(6);
		Cell cell87 = row8.createCell(7);
		Cell cell88 = row8.createCell(8);
		Cell cell89 = row8.createCell(9);
		Cell cell810 = row8.createCell(10);
		Cell cell811 = row8.createCell(11);
		Cell cell812 = row8.createCell(12);
		Cell cell813 = row8.createCell(13);
		Cell cell814 = row8.createCell(14);
		Cell cell815 = row8.createCell(15);
		Cell cell816 = row8.createCell(16);
		Cell cell817 = row8.createCell(17);
		Cell cell818 = row8.createCell(18);
		Cell cell819 = row8.createCell(19);
		Cell cell820 = row8.createCell(20);
		Cell cell821 = row8.createCell(21);
		Cell cell822 = row8.createCell(22);
		Cell cell823 = row8.createCell(23);
		Cell cell824 = row8.createCell(24);
		Cell cell825 = row8.createCell(25);
		Cell cell826 = row8.createCell(26);
		Cell cell827 = row8.createCell(27);
		Cell cell828 = row8.createCell(28);
		Cell cell829 = row8.createCell(29);
		Cell cell830 = row8.createCell(30);
		Cell cell831 = row8.createCell(31);
		Cell cell832 = row8.createCell(32);
		Cell cell833 = row8.createCell(33);
		Cell cell834 = row8.createCell(34);
		Cell cell835 = row8.createCell(35);

		cell80.setCellValue("Inv Type");
		cell81.setCellValue("Material Code");
		cell82.setCellValue("Part Desc");
		cell83.setCellValue("Qty");
		cell84.setCellValue("Amount");
		cell85.setCellValue("Qty");
		cell86.setCellValue("Amount");
		cell87.setCellValue("Qty");
		cell88.setCellValue("Amount");
		cell89.setCellValue("Qty");
		cell810.setCellValue("Amount");
		cell811.setCellValue("Qty");
		cell812.setCellValue("Amount");
		cell813.setCellValue("Qty");
		cell814.setCellValue("Amount");
		cell815.setCellValue("Qty");
		cell816.setCellValue("Amount");
		cell817.setCellValue("Qty");
		cell818.setCellValue("Amount");
		cell819.setCellValue("Qty");
		cell820.setCellValue("Amount");
		cell821.setCellValue("Qty");
		cell822.setCellValue("Amount");
		cell823.setCellValue("Qty");
		cell824.setCellValue("Amount");
		cell825.setCellValue("Qty");
		cell826.setCellValue("Amount");
		cell827.setCellValue("Qty");
		cell828.setCellValue("Amount");
		cell829.setCellValue("Qty");
		cell830.setCellValue("Amount");
		cell831.setCellValue("Qty");
		cell832.setCellValue("Amount");
		cell833.setCellValue("Qty");
		cell834.setCellValue("Average Rate"); 
		cell835.setCellValue("Value");
		
		cell80.setCellStyle(cs3);
		cell81.setCellStyle(cs3);
		cell82.setCellStyle(cs3);
		cell83.setCellStyle(cs4);
		cell84.setCellStyle(cs4);
		cell85.setCellStyle(cs3);
		cell86.setCellStyle(cs3);
		cell87.setCellStyle(cs3);
		cell88.setCellStyle(cs3);
		cell89.setCellStyle(cs3);
		cell810.setCellStyle(cs3);
		cell811.setCellStyle(cs3);
		cell812.setCellStyle(cs3);
		cell813.setCellStyle(cs3);
		cell814.setCellStyle(cs3);
		cell815.setCellStyle(cs3);
		cell816.setCellStyle(cs3);
		cell817.setCellStyle(cs3);
		cell818.setCellStyle(cs3);
		cell819.setCellStyle(cs3);
		cell820.setCellStyle(cs3);
		cell821.setCellStyle(cs3);
		cell822.setCellStyle(cs3);
		cell823.setCellStyle(cs3);
		cell824.setCellStyle(cs3);
		cell825.setCellStyle(cs3);
		cell826.setCellStyle(cs3);
		cell827.setCellStyle(cs3);
		cell828.setCellStyle(cs3);
		cell829.setCellStyle(cs3);
		cell830.setCellStyle(cs3);
		cell831.setCellStyle(cs3);
		cell832.setCellStyle(cs3);
		cell833.setCellStyle(cs9);
		cell834.setCellStyle(cs9);
		cell835.setCellStyle(cs9);
	
	}
	
	public static void createInvTypeWiseSheetHeaders(Workbook wb, String sheetName, String companyName, String valuationMethod, Date strStartDate, Date strEndDate){

		Sheet sheet = wb.getSheet(sheetName);
		/* added by ashish */
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 10));
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 35));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 11));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 14));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 15, 35));
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 35));
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 3));
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 3, 35));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 6, 35));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 0, 2));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 3, 4));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 5, 10));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 11, 13));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 14, 18));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 19, 23));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 24, 26));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 27, 32));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 33, 35));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 2));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 3, 4));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 5, 6));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 7, 8));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 9, 10));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 11, 12));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 13, 14));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 15, 16));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 17, 18));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 19, 20));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 21, 22));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 23, 24));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 25, 26));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 27, 28));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 29, 30));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 31, 32));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 33, 35));
		sheet.addMergedRegion(new CellRangeAddress(8, 8, 0, 2));
		/*  */ 
		
		CellStyle cs1 = wb.createCellStyle();
		CellStyle cs2 = wb.createCellStyle();
		CellStyle cs3 = wb.createCellStyle();
		CellStyle cs4 = wb.createCellStyle();
		CellStyle cs5 = wb.createCellStyle();
		CellStyle cs6 = wb.createCellStyle();
		CellStyle cs7 = wb.createCellStyle();
		CellStyle cs8 = wb.createCellStyle();
		CellStyle cs9 = wb.createCellStyle();

		f1 = wb.createFont();
		f1.setColor(HSSFColor.DARK_BLUE.index);
		f1.setBoldweight(Font.BOLDWEIGHT_BOLD);

		Row row0 = sheet.createRow(0); // ROW 0
		row0.createCell(0).setCellValue("Company Name:");
		Cell cell3 = row0.createCell(3);
		if(companyName.equals("RNAIPL"))
			cell3.setCellValue("RENAULT NISSAN AUTOMOTIVE INDIA PVT LTD");
		else if(companyName.equals("NMIPL"))
			cell3.setCellValue("NISSAN MOTOR INDIA PVT LTD");
		cs1.setFont(f1);
		cell3.setCellStyle(cs1);

		Row row1 = sheet.createRow(1); // ROW 1
		Cell cell112 = row1.createCell(12);
		cell112.setCellValue("STORES LEDGER - SUMMARY - PART WISE");
		cell112.setCellStyle(cs1);

		Row row2 = sheet.createRow(2); // ROW 2 (EMPTY for border)
		cs7.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);


		/*Row row3 = sheet.createRow(3); // ROW 3
		Cell cell30 = row3.createCell(0);
		cell30.setCellValue("STORES LEDGER FOR:");*/

		Row row3 = sheet.createRow(3); // ROW 3
		Cell cell30 = row3.createCell(0);
		cell30.setCellValue("STOCK VALUATION METHOD:"+valuationMethod);

		Row row4 = sheet.createRow(4); // ROW 4
		Cell cell40 = row4.createCell(0);
		Cell cell42 = row4.createCell(2);
		Cell dateCell1 = row4.createCell(3);
		Cell cell44 = row4.createCell(4);
		Cell dateCell2 = row4.createCell(5);
		cell40.setCellValue("PERIOD:");
		cell42.setCellValue("FROM:");
		
		 dateCell1.setCellValue(strStartDate);
		  dateCell1.setCellType(Cell.CELL_TYPE_STRING);
		  dateCell2.setCellType(Cell.CELL_TYPE_STRING);
		  dateCell2.setCellValue(strEndDate);
		/*
		 * Cell dateCell1 = row4.createCell(3); dateCell1.setCellValue(new
		 * Date()); CellStyle cellStyle3 = wb.createCellStyle();
		 * cellStyle3.setDataFormat
		 * (creationHelper.createDataFormat().getFormat("dd/mm/yyyy"));
		 * dateCell1.setCellStyle(cellStyle3);
		 */

		cell44.setCellValue("TO:");

		Row row5 = sheet.createRow(5); // ROW 5 (EMPTY for border)
		cs2.setBorderTop(HSSFBorderFormatting.BORDER_THICK);
		cs2.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);
		/* row5.setRowStyle(hcs2); */

		Row row6 = sheet.createRow(6); // ROW 6

		cs3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
		cs3.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs4.setFillForegroundColor(IndexedColors.PINK.getIndex());
		cs4.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs5.setFillForegroundColor(IndexedColors.AQUA.getIndex());
		cs5.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs6.setFillForegroundColor(IndexedColors.GREEN.getIndex());
		cs6.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs8.setFillForegroundColor(IndexedColors.RED.getIndex());
		cs8.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs9.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
		cs9.setFillPattern(CellStyle.SOLID_FOREGROUND);

		Cell cell60 = row6.createCell(0);
		Cell cell63 = row6.createCell(3);
		Cell cell611 = row6.createCell(11);
		Cell cell624 = row6.createCell(24);
		Cell cell633 = row6.createCell(33);

		cell60.setCellValue("STORES LEDGER REFERENCE");
		cell63.setCellValue("STOCK BALANCE");
		cell611.setCellValue("PURCHASE/ RECEIPTS");
		cell624.setCellValue("SALE/ CONSUMPTION");
		cell633.setCellValue("STOCK BALANCE");

		cell60.setCellStyle(cs3);
		cell63.setCellStyle(cs4);
		cell611.setCellStyle(cs5);
		cell624.setCellStyle(cs8);
		cell633.setCellStyle(cs6);
		
		row6.createCell(5).setCellStyle(cs5);
		row6.createCell(6).setCellStyle(cs5);
		row6.createCell(7).setCellStyle(cs5);
		row6.createCell(8).setCellStyle(cs5);
		row6.createCell(9).setCellStyle(cs5);
		row6.createCell(10).setCellStyle(cs5);
		row6.createCell(14).setCellStyle(cs5);
		row6.createCell(15).setCellStyle(cs5);
		row6.createCell(16).setCellStyle(cs5);
		row6.createCell(17).setCellStyle(cs5);
		row6.createCell(18).setCellStyle(cs5);
		row6.createCell(10).setCellStyle(cs8);
		row6.createCell(19).setCellStyle(cs8);
		row6.createCell(20).setCellStyle(cs8);
		row6.createCell(21).setCellStyle(cs8);
		row6.createCell(22).setCellStyle(cs8);
		row6.createCell(23).setCellStyle(cs8);
		row6.createCell(27).setCellStyle(cs8);
		row6.createCell(28).setCellStyle(cs8);
		row6.createCell(29).setCellStyle(cs8);
		row6.createCell(30).setCellStyle(cs8);
		
		
		Row row7 = sheet.createRow(7); // ROW 7
		
		row7.createCell(0).setCellStyle(cs3);
		row7.createCell(1).setCellStyle(cs3);
		row7.createCell(2).setCellStyle(cs3);
		
		Cell cell73 = row7.createCell(3);
		row7.createCell(4);
		Cell cell75 = row7.createCell(5);
		row7.createCell(6);
		Cell cell77 = row7.createCell(7);
		row7.createCell(8);
		Cell cell79 = row7.createCell(9);
		row7.createCell(10);
		Cell cell711 = row7.createCell(11);
		row7.createCell(12);
		Cell cell713 = row7.createCell(13);
		row7.createCell(14);
		Cell cell715 = row7.createCell(15);
		row7.createCell(16);
		Cell cell717 = row7.createCell(17);
		row7.createCell(18);
		Cell cell719 = row7.createCell(19);
		row7.createCell(20);
		Cell cell721 = row7.createCell(21);
		row7.createCell(22);
		Cell cell723 = row7.createCell(23);
		row7.createCell(24);
		Cell cell725 = row7.createCell(25);
		row7.createCell(26);
		Cell cell727 = row7.createCell(27);
		row7.createCell(28);
		Cell cell729 = row7.createCell(29);
		row7.createCell(30);
		Cell cell731 = row7.createCell(31);
		row7.createCell(32);
		Cell cell733 = row7.createCell(33);
		row7.createCell(34);
		row7.createCell(35);
		
		
		cell73.setCellValue("OPENING BOOK STOCK");
		cell75.setCellValue("ZGRIR");
		cell77.setCellValue("CUSTOM");
		cell79.setCellValue("COSTPRICE");
		cell711.setCellValue("INVADJ");
		cell713.setCellValue("FOREX");
		cell715.setCellValue("FI ADJ");
		cell717.setCellValue("RETRO");
		
		cell719.setCellValue("SALES");
		
		cell721.setCellValue("CONS");
		cell723.setCellValue("FOC");
		cell725.setCellValue("RTV");
		cell727.setCellValue("SCRAP");
		cell729.setCellValue("CAPTIVE");
		cell731.setCellValue("OTHER CONS");
		cell733.setCellValue("CLOSING BOOK STOCK");
		
		cell73.setCellStyle(cs4);
		cell75.setCellStyle(cs3);
		cell77.setCellStyle(cs3);
		cell79.setCellStyle(cs3);
		cell711.setCellStyle(cs3);
		cell713.setCellStyle(cs3);
		cell715.setCellStyle(cs3);
		cell717.setCellStyle(cs3);
		cell719.setCellStyle(cs9);
		cell721.setCellStyle(cs9);
		cell723.setCellStyle(cs9);
		cell723.setCellStyle(cs9);
		cell725.setCellStyle(cs9);
		cell727.setCellStyle(cs9);
		cell729.setCellStyle(cs9);
		cell731.setCellStyle(cs9);
		cell733.setCellStyle(cs6);
		
		
		Row row8 = sheet.createRow(8); // ROW 8

		Cell cell80 = row8.createCell(0);
		Cell cell81 = row8.createCell(1);
		Cell cell82 = row8.createCell(2);
		Cell cell83 = row8.createCell(3);
		Cell cell84 = row8.createCell(4);
		Cell cell85 = row8.createCell(5);
		Cell cell86 = row8.createCell(6);
		Cell cell87 = row8.createCell(7);
		Cell cell88 = row8.createCell(8);
		Cell cell89 = row8.createCell(9);
		Cell cell810 = row8.createCell(10);
		Cell cell811 = row8.createCell(11);
		Cell cell812 = row8.createCell(12);
		Cell cell813 = row8.createCell(13);
		Cell cell814 = row8.createCell(14);
		Cell cell815 = row8.createCell(15);
		Cell cell816 = row8.createCell(16);
		Cell cell817 = row8.createCell(17);
		Cell cell818 = row8.createCell(18);
		Cell cell819 = row8.createCell(19);
		Cell cell820 = row8.createCell(20);
		Cell cell821 = row8.createCell(21);
		Cell cell822 = row8.createCell(22);
		Cell cell823 = row8.createCell(23);
		Cell cell824 = row8.createCell(24);
		Cell cell825 = row8.createCell(25);
		Cell cell826 = row8.createCell(26);
		Cell cell827 = row8.createCell(27);
		Cell cell828 = row8.createCell(28);
		Cell cell829 = row8.createCell(29);
		Cell cell830 = row8.createCell(30);
		Cell cell831 = row8.createCell(31);
		Cell cell832 = row8.createCell(32);
		Cell cell833 = row8.createCell(33);
		Cell cell834 = row8.createCell(34);
		Cell cell835 = row8.createCell(35);

		cell80.setCellValue("Inv Type");
		//cell81.setCellValue("Material Code");
		//cell82.setCellValue("Part Desc");
		cell83.setCellValue("Qty");
		cell84.setCellValue("Amount");
		cell85.setCellValue("Qty");
		cell86.setCellValue("Amount");
		cell87.setCellValue("Qty");
		cell88.setCellValue("Amount");
		cell89.setCellValue("Qty");
		cell810.setCellValue("Amount");
		cell811.setCellValue("Qty");
		cell812.setCellValue("Amount");
		cell813.setCellValue("Qty");
		cell814.setCellValue("Amount");
		cell815.setCellValue("Qty");
		cell816.setCellValue("Amount");
		cell817.setCellValue("Qty");
		cell818.setCellValue("Amount");
		cell819.setCellValue("Qty");
		cell820.setCellValue("Amount");
		cell821.setCellValue("Qty");
		cell822.setCellValue("Amount");
		cell823.setCellValue("Qty");
		cell824.setCellValue("Amount");
		cell825.setCellValue("Qty");
		cell826.setCellValue("Amount");
		cell827.setCellValue("Qty");
		cell828.setCellValue("Amount");
		cell829.setCellValue("Qty");
		cell830.setCellValue("Amount");
		cell831.setCellValue("Qty");
		cell832.setCellValue("Amount");
		cell833.setCellValue("Qty");
		cell834.setCellValue("Average Rate"); 
		cell835.setCellValue("Value");
		
		cell80.setCellStyle(cs3);
		//cell81.setCellStyle(cs3);
		//cell82.setCellStyle(cs3);
		cell83.setCellStyle(cs4);
		cell84.setCellStyle(cs4);
		cell85.setCellStyle(cs3);
		cell86.setCellStyle(cs3);
		cell87.setCellStyle(cs3);
		cell88.setCellStyle(cs3);
		cell89.setCellStyle(cs3);
		cell810.setCellStyle(cs3);
		cell811.setCellStyle(cs3);
		cell812.setCellStyle(cs3);
		cell813.setCellStyle(cs3);
		cell814.setCellStyle(cs3);
		cell815.setCellStyle(cs3);
		cell816.setCellStyle(cs3);
		cell817.setCellStyle(cs3);
		cell818.setCellStyle(cs3);
		cell819.setCellStyle(cs3);
		cell820.setCellStyle(cs3);
		cell821.setCellStyle(cs3);
		cell822.setCellStyle(cs3);
		cell823.setCellStyle(cs3);
		cell824.setCellStyle(cs3);
		cell825.setCellStyle(cs3);
		cell826.setCellStyle(cs3);
		cell827.setCellStyle(cs3);
		cell828.setCellStyle(cs3);
		cell829.setCellStyle(cs3);
		cell830.setCellStyle(cs3);
		cell831.setCellStyle(cs3);
		cell832.setCellStyle(cs3);
		cell833.setCellStyle(cs9);
		cell834.setCellStyle(cs9);
		cell835.setCellStyle(cs9);
	
	}
	
	public static void createVendorWiseSheetHeaders(Workbook wb, String sheetName, String companyName, Date strStartDate, Date strEndDate){

		
		Sheet sheet = wb.getSheet(sheetName);
		
		/* added by ashish */
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 10));
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 16));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 11));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 14));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 15, 16));
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 24));
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 6, 16));
		sheet.addMergedRegion(new CellRangeAddress(5, 5, 6, 16));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 4));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 5, 8));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 9, 13));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 14, 16));
		/*sheet.addMergedRegion(new CellRangeAddress(7, 7, 20, 21));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 22, 24));*/
		/*  */
		
		CellStyle cs1 = wb.createCellStyle();
		CellStyle cs2 = wb.createCellStyle();
		CellStyle cs3 = wb.createCellStyle();
		CellStyle cs4 = wb.createCellStyle();
		CellStyle cs5 = wb.createCellStyle();
		CellStyle cs6 = wb.createCellStyle();
		CellStyle cs7 = wb.createCellStyle();

		f1 = wb.createFont();
		f1.setColor(HSSFColor.DARK_BLUE.index);
		f1.setBoldweight(Font.BOLDWEIGHT_BOLD);

		Row row0 = sheet.createRow(0); // ROW 0
		row0.createCell(0).setCellValue("Company Name:");
		Cell cell3 = row0.createCell(3);
		if(companyName.equals("RNAIPL"))
			cell3.setCellValue("RENAULT NISSAN AUTOMOTIVE INDIA PVT LTD");
		else if(companyName.equals("NMIPL"))
			cell3.setCellValue("NISSAN MOTOR INDIA PVT LTD");
		cs1.setFont(f1);
		cell3.setCellStyle(cs1);

		Row row1 = sheet.createRow(1); // ROW 1
		Cell cell112 = row1.createCell(12);
		cell112.setCellValue("VENDOR WISE PURCHASE REPORT");
		cell112.setCellStyle(cs1);

		Row row2 = sheet.createRow(2); // ROW 2 (EMPTY for border)
		cs7.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);

		Row row3 = sheet.createRow(3); // ROW 3
		Cell cell30 = row3.createCell(0);
		Cell cell32 = row3.createCell(2);
		
		Cell dateCell1 = row3.createCell(3);
		Cell cell34 = row3.createCell(4);
		Cell dateCell2 = row3.createCell(5);
		cell30.setCellValue("PERIOD:");
		cell32.setCellValue("FROM:");
		
		
/*
		CreationHelper creationHelper = wb.getCreationHelper();
		creationHelper.createDataFormat().*/
		  dateCell1.setCellValue(strStartDate);
		  dateCell1.setCellType(Cell.CELL_TYPE_STRING);
		  
		  cell34.setCellValue("TO:");
		  
		  dateCell2.setCellValue(strEndDate);
		  dateCell2.setCellType(Cell.CELL_TYPE_STRING);
		  
		// CellStyle cellStyle3 = wb.createCellStyle();
		 
		 /*(creationHelper.createDataFormat().getFormat("dd/mm/yyyy"));
		 dateCell1.setCellStyle(cellStyle3);*/
		

		

		Row row5 = sheet.createRow(5); // ROW 5 (EMPTY for border)
		cs2.setBorderTop(HSSFBorderFormatting.BORDER_THICK);
		cs2.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);
		/* row5.setRowStyle(hcs2); */

		Row row7 = sheet.createRow(7); // ROW 7

		cs3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
		cs3.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs4.setFillForegroundColor(IndexedColors.PINK.getIndex());
		cs4.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs5.setFillForegroundColor(IndexedColors.AQUA.getIndex());
		cs5.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs6.setFillForegroundColor(IndexedColors.GREEN.getIndex());
		cs6.setFillPattern(CellStyle.SOLID_FOREGROUND);

		Cell cell75 = row7.createCell(5);
		Cell cell714 = row7.createCell(14);

		cell75.setCellValue("STORES LEDGER REFERENCE");
		cell714.setCellValue("PURCHASE/ RECEIPTS");

		cell75.setCellStyle(cs3);
		cell714.setCellStyle(cs4);

		row7.createCell(0).setCellStyle(cs3);
		row7.createCell(1).setCellStyle(cs3);
		row7.createCell(2).setCellStyle(cs3);
		row7.createCell(3).setCellStyle(cs3);
		row7.createCell(4).setCellStyle(cs3);
		//row7.createCell(5).setCellStyle(cs3);
		row7.createCell(6).setCellStyle(cs3);
		row7.createCell(7).setCellStyle(cs3);
		row7.createCell(8).setCellStyle(cs3);
		row7.createCell(9).setCellStyle(cs3);
		row7.createCell(10).setCellStyle(cs3);
		row7.createCell(11).setCellStyle(cs3);
		row7.createCell(12).setCellStyle(cs3);
		row7.createCell(13).setCellStyle(cs3);
		row7.createCell(15).setCellStyle(cs4);
		row7.createCell(16).setCellStyle(cs4);
		

		Row row8 = sheet.createRow(8); // ROW 8

		Cell cell80 = row8.createCell(0);
		Cell cell81 = row8.createCell(1);
		Cell cell82 = row8.createCell(2);
		Cell cell83 = row8.createCell(3);
		Cell cell84 = row8.createCell(4);
		Cell cell85 = row8.createCell(5);
		Cell cell86 = row8.createCell(6);
		Cell cell87 = row8.createCell(7);
		Cell cell88 = row8.createCell(8);
		Cell cell89 = row8.createCell(9);
		Cell cell810 = row8.createCell(10);
		Cell cell811 = row8.createCell(11);
		Cell cell812 = row8.createCell(12);
		Cell cell813 = row8.createCell(13);
		Cell cell814 = row8.createCell(14);
		Cell cell815 = row8.createCell(15);
		Cell cell816 = row8.createCell(16);

		cell80.setCellValue("Inv Type");
		cell81.setCellValue("Material Code");
		cell82.setCellValue("Part Desc");
		cell83.setCellValue("SAP Doc No");
		cell84.setCellValue("Entry Date");
		cell85.setCellValue("Assignment No");
		cell86.setCellValue("Transaction Date");
		cell87.setCellValue("Vendor Invoice No");
		cell88.setCellValue("Invoice Date");
		cell89.setCellValue("Amount in FCY");
		cell810.setCellValue("FCY Unit");
		cell811.setCellValue("Purchase Type");
		cell812.setCellValue("Transaction Type");
		cell813.setCellValue("AcctType");
		cell814.setCellValue("Qty");
		cell815.setCellValue("Unit Rate");
		cell816.setCellValue(" Amount in Local Currency");
				
		cell80.setCellStyle(cs3);
		cell81.setCellStyle(cs3);
		cell82.setCellStyle(cs3);
		cell83.setCellStyle(cs3);
		cell84.setCellStyle(cs3);
		cell85.setCellStyle(cs3);
		cell86.setCellStyle(cs3);
		cell87.setCellStyle(cs3);
		cell88.setCellStyle(cs3);
		cell89.setCellStyle(cs3);
		cell810.setCellStyle(cs3);
		cell811.setCellStyle(cs3);
		cell812.setCellStyle(cs3);
		cell813.setCellStyle(cs3);
		cell814.setCellStyle(cs4);
		cell815.setCellStyle(cs4);
		cell816.setCellStyle(cs4);
	}
	
	public static Workbook generateNegativePartExcel(String sheetName, Date startDate, Date endDate, String companyName){

		HSSFWorkbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet(sheetName);
		 
		String strStartDate =simpleDateFormat.format(startDate);
		String strEndDate =simpleDateFormat.format(endDate);
		
		/* added by ashish */
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 10));
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 24));
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 24));
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 24));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 24));
		sheet.addMergedRegion(new CellRangeAddress(5, 5, 6, 24));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 2));
		/*  */
		
		CellStyle cs1 = wb.createCellStyle();
		CellStyle cs2 = wb.createCellStyle();
		CellStyle cs3 = wb.createCellStyle();
		CellStyle cs7 = wb.createCellStyle();
		
		f1 = wb.createFont();
		f1.setColor(HSSFColor.DARK_BLUE.index);
		f1.setBoldweight(Font.BOLDWEIGHT_BOLD);

		Row row0 = sheet.createRow(0); // ROW 0
		row0.createCell(0).setCellValue("Company Name:");
		Cell cell3 = row0.createCell(3);
		if(companyName.equals("RNAIPL"))
			cell3.setCellValue("RENAULT NISSAN AUTOMOTIVE INDIA PVT LTD");
		else if(companyName.equals("NMIPL"))
			cell3.setCellValue("NISSAN MOTOR INDIA PVT LTD");
		cs1.setFont(f1);
		cell3.setCellStyle(cs1);

		Row row1 = sheet.createRow(1); // ROW 1
		Cell cell11 = row1.createCell(1);
		cell11.setCellValue("NEGATIVE PART ANALYSIS");
		cell11.setCellStyle(cs1);

		Row row2 = sheet.createRow(2); // ROW 2 (EMPTY for border)
		cs7.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);

		Row row5 = sheet.createRow(5); // ROW 5
		Cell cell50 = row5.createCell(0);
		Cell cell52 = row5.createCell(2);
		
		Cell dateCell1 = row5.createCell(3);
		Cell cell54 = row5.createCell(4);
		Cell dateCell2 = row5.createCell(5);
		cell50.setCellValue("PERIOD:");
		cell52.setCellValue("FROM:");
		
		  dateCell1.setCellValue(strStartDate);
		  dateCell1.setCellType(Cell.CELL_TYPE_STRING);
		  dateCell2.setCellType(Cell.CELL_TYPE_STRING);
		  dateCell2.setCellValue(strEndDate);

		cell54.setCellValue("TO:");

		Row row6 = sheet.createRow(6); // ROW 6 (EMPTY for border)
		cs2.setBorderTop(HSSFBorderFormatting.BORDER_THICK);
		cs2.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);
		/* row6.setRowStyle(hcs2); */

		Row row7 = sheet.createRow(7); // ROW 7

		cs3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
		cs3.setFillPattern(CellStyle.SOLID_FOREGROUND);
		

		Cell cell70 = row7.createCell(0);

		cell70.setCellValue("STORES LEDGER REFERENCE");

		cell70.setCellStyle(cs3);
		
		Row row8 = sheet.createRow(8); // ROW 8

		Cell cell80 = row8.createCell(0);
		Cell cell81 = row8.createCell(1);
		Cell cell82 = row8.createCell(2);
	
		cell80.setCellValue("Inv Type");
		cell81.setCellValue("Material Code");
		cell82.setCellValue("Part Desc");

		cell80.setCellStyle(cs3);
		cell81.setCellStyle(cs3);
		cell82.setCellStyle(cs3);
	
		//logger.debug("Negative Part Default Sheet Created");
		
		return wb;
	}
	public static Integer appendNegativePartExcelSheet(Workbook workbook, Integer excelRowCounter, List<FIFOReportDTO> fifoReportDTOs, ReportGenerateForm reportForm){
		
		
		String sheetName = ApplicationConstants.negativePartSheetName;
		
		if(workbook.getNumberOfSheets() > 1){
			sheetName = ApplicationConstants.negativePartSheetName + "_" + (workbook.getNumberOfSheets()-1);
		}else{
			sheetName = ApplicationConstants.negativePartSheetName; 
		}
		
		Sheet sheet = workbook.getSheet(ApplicationConstants.negativePartSheetName);
		
		for(FIFOReportDTO fifoReportDTO : fifoReportDTOs){
			
			if(excelRowCounter > 50000){
				sheetName = ApplicationConstants.negativePartSheetName + "_" + workbook.getNumberOfSheets();
				workbook.createSheet(sheetName);
				createNegativePartSheetHeaders(sheetName, reportForm.getStartDate(), reportForm.getEndDate(), reportForm.getCompanyName());
				excelRowCounter = 10;
				sheet = workbook.getSheet(sheetName);
			}
			
			Row row = sheet.createRow(excelRowCounter);
			Cell cell0 = row.createCell(0);
			Cell cell1 = row.createCell(1);
			Cell cell2 = row.createCell(2);
						
			if(fifoReportDTO.getInventoryType()!=null)
				cell0.setCellValue(fifoReportDTO.getInventoryType());
			
			if(fifoReportDTO.getPartId()!=null)
				cell1.setCellValue(fifoReportDTO.getPartId());
			
			if(fifoReportDTO.getPartDesc()!=null)
				cell2.setCellValue(fifoReportDTO.getPartDesc());
			
			excelRowCounter++;
			//System.out.println("Excel Row Counter: " + excelRowCounter);
		}


		//logger.debug("Negative Part Analysis Sheet Appended");
		
		return excelRowCounter ++;
	}
	
	public static void createNegativePartSheetHeaders(String sheetName, Date startDate, Date endDate, String companyName){


		HSSFWorkbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet(sheetName);
		 
		String strStartDate =simpleDateFormat.format(startDate);
		String strEndDate =simpleDateFormat.format(endDate);
		
		/* added by ashish */
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 10));
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 24));
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 24));
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 24));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 24));
		sheet.addMergedRegion(new CellRangeAddress(5, 5, 6, 24));
		sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 3));
		/*  */
		
		CellStyle cs1 = wb.createCellStyle();
		CellStyle cs2 = wb.createCellStyle();
		CellStyle cs3 = wb.createCellStyle();
		CellStyle cs7 = wb.createCellStyle();
		
		f1 = wb.createFont();
		f1.setColor(HSSFColor.DARK_BLUE.index);
		f1.setBoldweight(Font.BOLDWEIGHT_BOLD);

		Row row0 = sheet.createRow(0); // ROW 0
		row0.createCell(0).setCellValue("Company Name:");
		Cell cell3 = row0.createCell(3);
		if(companyName.equals("RNAIPL"))
			cell3.setCellValue("RENAULT NISSAN AUTOMOTIVE INDIA PVT LTD");
		else if(companyName.equals("NMIPL"))
			cell3.setCellValue("NISSAN MOTOR INDIA PVT LTD");
		cs1.setFont(f1);
		cell3.setCellStyle(cs1);

		Row row1 = sheet.createRow(1); // ROW 1
		Cell cell11 = row1.createCell(1);
		cell11.setCellValue("NEGATIVE PART ANALYSIS");
		cell11.setCellStyle(cs1);

		Row row2 = sheet.createRow(2); // ROW 2 (EMPTY for border)
		cs7.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);

		Row row5 = sheet.createRow(5); // ROW 5
		Cell cell50 = row5.createCell(0);
		Cell cell52 = row5.createCell(2);
		
		Cell dateCell1 = row5.createCell(3);
		Cell cell54 = row5.createCell(4);
		Cell dateCell2 = row5.createCell(5);
		cell50.setCellValue("PERIOD:");
		cell52.setCellValue("FROM:");
		
		  dateCell1.setCellValue(strStartDate);
		  dateCell1.setCellType(Cell.CELL_TYPE_STRING);
		  dateCell2.setCellType(Cell.CELL_TYPE_STRING);
		  dateCell2.setCellValue(strEndDate);

		cell54.setCellValue("TO:");

		Row row6 = sheet.createRow(6); // ROW 6 (EMPTY for border)
		cs2.setBorderTop(HSSFBorderFormatting.BORDER_THICK);
		cs2.setBorderBottom(HSSFBorderFormatting.BORDER_THICK);
		/* row6.setRowStyle(hcs2); */

		Row row7 = sheet.createRow(7); // ROW 7

		cs3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
		cs3.setFillPattern(CellStyle.SOLID_FOREGROUND);
		

		Cell cell70 = row7.createCell(0);

		cell70.setCellValue("STORES LEDGER REFERENCE");

		cell70.setCellStyle(cs3);

		row7.createCell(0).setCellStyle(cs3);
		
		Row row8 = sheet.createRow(8); // ROW 8

		Cell cell80 = row8.createCell(0);
		Cell cell81 = row8.createCell(1);
		Cell cell82 = row8.createCell(2);
	
		cell80.setCellValue("Inv Type");
		cell81.setCellValue("Material Code");
		cell82.setCellValue("Part Desc");

		cell80.setCellStyle(cs3);
		cell81.setCellStyle(cs3);
		cell82.setCellStyle(cs3);
	
		System.out.println("Headers Generated");
	}
}
